user3438447 user3438447 - 16 days ago 8
SQL Question

SQL concatenate one of the columns

I have the following output:

portfolio name | accounts | segment | number
A | USD | seg1 | 23
A | CNY | seg2 | null
A | EUR | seg2 | null
B | EUR | seg2 | null
B | USD | seg2 | null
B | EUR | seg3 | 24


And I would like to basically concatenate all the rows according to portfolio name, same segment and same number. As you can see sometimes number can be null as well.

Basically the end output i want for this example is:

A | USD | seg1 | 23
A | CNY, EUR | seg2 | null
B | EUR, USD | seg2 | null
B | EUR | seg3 | 24


I've tried using listagg but can't figure it out...

Hope you understand my question.

Answer

If you are using Oracle (You said you tried to use LISTAGG) then :

SELECT t.portfolio_name, t.segment, 
       LISTAGG(COALESCE(t.accounts,''),',') WITHIN (ORDER BY t.accounts) as segments,
       (SELECT LISTAGG(COALESCE(s.number,''),',') WITHIN (ORDER BY s.number) 
        FROM (SELECT DISTINCT portfolio_name,segment,number
              FROM YourTable) s
        WHERE t.portfolio_name = s.portfolio_name
          AND t.segment = s.segment ) as numbers
FROM YourTable t
GROUP BY t.portfolio_name, t.segment