Group By with row values as column name and group concat-CodePudding

I am searching for a solution for the SQL problem:

I have input table like this:

RId	AId	Type
76	734	TKI
76	528	NPlat
76	735	TKI
77	713	Plat
77	749	IO
77	739	TKI
77	714	NPlat
78	518	Plat
73	519	Plat
73	518	Plat

And I want this kind of output:

RId	TKI	IO	NPlat	Plat
73				518, 519
76	734, 735		528
77	739	749	714	713
78				518

I tried with PIVOT, but it's not working. Also tried with the GROUP BY and PARTITION BY together, but no success.

Can anybody have any idea to solve this?

Note: I am using Microsoft SQL Server 2016 (SP3).

CodePudding user response：

You can concatenate the Aid into csv format first and then perform the pivot

select *
from   (
           select  Rid, [Type], 
                   Aid = stuff(
                             (select ','   convert(varchar(10), x.Aid)
                             from   tbl x 
                             where  x.Rid = t.Rid
                             and    x.[Type] = t.[Type]
                             order by x.Aid
                             for xml path('')), 1, 1, '')
           from    tbl t
           group by Rid, [Type]
       ) d
       pivot
       (
            max(Aid)
            for [Type] in ([TKI], [IO], [NPlat], [Plat])
       ) p

dbfiddle demo

CodePudding user response：

Simple Use Stuff with Pivot :

SELECT RID,[TKI],[IO],[NPlat],[Plat]
FROM 
(SELECT RID,TYPE,AID = STUFF((
                                SELECT ','   CONVERT(VARCHAR,AID)
                                FROM test t
                                WHERE t.RID = test.RID AND t.TYPE = test.type                               
                                FOR XML PATH('')
                                ), 1, 1, '')
FROM test 
)sorce
PIVOT 
( 
max(AID) FOR type IN ([TKI],[IO],[NPlat],[Plat]) 
) AS PivotTable

CodePudding user response：

SQL Server 2016:

A possible solution is to group and aggregate:

Table:

SELECT *
INTO Data
FROM (VALUES
   (76, 734, 'TKI'),
   (76, 528, 'NPlat'),
   (76, 735, 'TKI'),  
   (77, 713, 'Plat'), 
   (77, 749, 'IO'),   
   (77, 739, 'TKI'),  
   (77, 714, 'NPlat'),
   (78, 518, 'Plat'), 
   (73, 519, 'Plat'), 
   (73, 518, 'Plat')
) v (RId, AId, [Type])

Statement:

SELECT 
   d.Rid, 
   TKI = STUFF(
      (
      SELECT CONCAT(',', AId) 
      FROM Data
      WHERE RId = d.RId AND [Type] = 'TKI'
      ORDER BY AId
      FOR XML PATH('')
      ), 1, 1, ''
   ),
   IO = STUFF(
      (
      SELECT CONCAT(',', AId) 
      FROM Data
      WHERE RId = d.RId AND [Type] = 'IO'
      ORDER BY AId
      FOR XML PATH('')
      ), 1, 1, ''
   ),
   NPlat = STUFF(
      (
      SELECT CONCAT(',', AId) 
      FROM Data
      WHERE RId = d.RId AND [Type] = 'NPlat'
      ORDER BY AId
      FOR XML PATH('')
      ), 1, 1, ''
   ),
   Plat = STUFF(
      (
      SELECT CONCAT(',', AId) 
      FROM Data
      WHERE RId = d.RId AND [Type] = 'Plat'
      ORDER BY AId
      FOR XML PATH('')
      ), 1, 1, ''
   )
FROM Data d 
GROUP BY d.RId

SQL Server 2017 :

PIVOT and STRING_AGG() is probably the first option:

SELECT *
FROM (
   SELECT RId, STRING_AGG(AId, ',') WITHIN GROUP (ORDER BY AId) AS AId, [Type]
   FROM Data
   GROUP BY RId, [Type]
) t 
PIVOT (
   MAX(AId)
   FOR [Type] IN ([TKI], [IO], [NPLat], [Plat]) 
) p