Pivot strings in SQl on Identifier

Question

I have two columns in sql

        ID      Perils

        1        PES
        1        PEA
        2        PAL
        2        PWH

I wanted a query which can get me

        ID      Perils

        1        PES+PEA
        2        PAL+PWH

Hence concatenating string with delimitor '+' by pivoting on ID

score 1 · Answer 1 · answered Nov 18 '15 at 16:38

1

Group by ID column and concatenate the Perils column. Now the aggregate for doing this is DB-specific. For MySQL, it would be:

select ID, GROUP_CONCAT(Perils) as Perils
from theTable
group by ID

the method for Oracle is listagg().

answered Nov 18 '15 at 16:38

DBug

What about Sql Server, I am doing in SSMS – Vasista B Nov 18 '15 at 16:39
SSMS you can use FOR XML PATH like here: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server – A. Greensmith Nov 18 '15 at 16:41

1 Answers1