CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
lms
lms

Reputation: 93

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

Upvotes: 2

Views: 6084

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 2

Mihai
Mihai

Reputation: 26784

SELECTid,COUNT(Systemid) as SystemidCount,
SystemName=Stuff((SELECT ',' + SystemName FROM t t1 WHERE t1.id=t.id
 FOR XML PATH (''))
             , 1, 1, '' )
FROM t
GROUP BY id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This is string aggregation:

select id, count(*) as systemidcount,
       stuff(v.systemnames, 1, 1, '') as systemnames
from t cross apply
     (select ',' + systemName
      from t t2
      where t2.id = t.id
      for xml path ('')
     ) v(systemnames);

Upvotes: 1

Related Questions