CODEWITHSUNDEEP
sqlpostgresqlstring-aggregation
fhcoso
fhcoso

Reputation: 585

Advanced merge/formatting in SQL

I have a query with a 2 columns output. I need to do advanced merge. By default, I'm in schema "public" but I need to do the select in schema "comm".

SELECT distinct "tata"
    , "toto" 
FROM comm.tata, comm.toto 
WHERE (comm.fid = comm.tid);`

I obtain: tata | toto

00045756-58e7-4383-9757-3e4134c8ede5 | 7ecfb576-a356-452e-bd66-b18964de4ac6
00045756-58e7-4383-9757-3e4134c8ede5 | a1889fb5-90a4-4f8c-ba0a-f81f1aff1815
00045756-58e7-4383-9757-3e4134c8ede5 | e3286260-7b47-4525-acd2-f625000a7ba1
00159e48-4d67-42ff-ac0e-d1d641ce44b2 | 2d4c1fa7-3ef0-458d-a5f0-15d9c935ea20
00159e48-4d67-42ff-ac0e-d1d641ce44b2 | 7ecfb576-a356-452e-bd66-b18964de4ac6
00159e48-4d67-42ff-ac0e-d1d641ce44b2 | 8c5e2d64-6cd1-4a29-8063-7fec301c0236
00159e48-4d67-42ff-ac0e-d1d641ce44b2 | 8cb2a705-4df6-4013-8e56-cc52b6ae4bdb
00159e48-4d67-42ff-ac0e-d1d641ce44b2 | a1889fb5-90a4-4f8c-ba0a-f81f1aff1815
00159e48-4d67-42ff-ac0e-d1d641ce44b2 | bca48f90-4b4c-4fa7-9e2a-44c045527484
00159e48-4d67-42ff-ac0e-d1d641ce44b2 | e3286260-7b47-4525-acd2-f625000a7ba1
002a8438-ad57-4e88-8cd5-2270d82dc7bd | 8c5e2d64-6cd1-4a29-8063-7fec301c0236
002a8438-ad57-4e88-8cd5-2270d82dc7bd | 8cb2a705-4df6-4013-8e56-cc52b6ae4bdb

I want instead:tata | toto

00045756-58e7-4383-9757-3e4134c8ede5 | 7ecfb576-a356-452e-bd66-b18964de4ac6, a1889fb5-90a4-4f8c-ba0a-f81f1aff1815, e3286260-7b47-4525-acd2-f625000a7ba1
00159e48-4d67-42ff-ac0e-d1d641ce44b2 | 2d4c1fa7-3ef0-458d-a5f0-15d9c935ea20, 7ecfb576-a356-452e-bd66-b18964de4ac6, 8c5e2d64-6cd1-4a29-8063-7fec301c0236, 8cb2a705-4df6-4013-8e56-cc52b6ae4bdb, a1889fb5-90a4-4f8c-ba0a-f81f1aff1815, bca48f90-4b4c-4fa7-9e2a-44c045527484, e3286260-7b47-4525-acd2-f625000a7ba1
002a8438-ad57-4e88-8cd5-2270d82dc7bd | 8c5e2d64-6cd1-4a29-8063-7fec301c0236, 8cb2a705-4df6-4013-8e56-cc52b6ae4bdb

I don't remember how I can obtain this merge/concat of result where I have on 1 row, all results of toto for tata

Upvotes: 0

Views: 61

Answers (1)

GMB
GMB

Reputation: 222402

You can use string_agg():

select a.cola, string_agg(o.colo, ', ' order by o.colo) 
from tata a
inner join toto o on o.colo = a.cola

Depending on your purpose, you might also want to consider array_agg(): it is often handier to manipulate arrays than CSV lists.

Upvotes: 2

Related Questions