lms lms - 27 days ago 15
SQL Question

SQL group by and cross apply

id | systemName | Systemid
-------------------------------
100 | A100 | 1
100 | B100 | 2
100 | C100 | 3
100 | D100 | 4
200 | A100 | 1
200 | B200 | 2


What is the best way to achieve the below result? Column System name should have the comma separated values of counted systems

id Systemidcount SystemName
---------------------------------------------
100 | 4 | A100,B100,C100,D100
200 | 2 | A100,B200


I am not able to format it correctly for some reason, apologies

Answer

You may notice the sub-query alias A. This is to prevent redundant calls in the CROSS APPLY

Declare @YourTable table (id int,systemname varchar(25),systemid int)
Insert Into @YourTable values 
(100,'A100',1),
(100,'B100',2),
(100,'C100',3),
(100,'D100',4),
(200,'A100',1),
(200,'B200',2)

Select A.*
      ,SystemName = B.Value
 From  (Select ID,Systemidcount = count(*) From @YourTable Group By ID) A
 Cross Apply (
               Select Value=Stuff((Select Distinct ',' + systemname 
                      From  @YourTable 
                      Where ID=A.ID 
                      For XML Path ('')),1,1,'') 

             ) B
 Order By ID

Returns

ID  Systemidcount   SystemName
100 4               A100,B100,C100,D100
200 2               A100,B200
Comments