Group together the records by column

Question

This is the table which has different technology with the same id

id   technology       
------------------
1    MCA           
1    BSC SCIENCE   
2    BTECH         
3    BTECH         
3    MTECH

How can I get the output group together as:

id   technology       
--------------------------
1    MCA, BSC SCIENCE               
2    BTECH         
3    BTECH, MTECH

What have you tried, what is the result of your research, what SPECIFIC problem you cannot solve? Please read [ask] and [mcve] in [help] — Pred, Dec 20 '17 at 10:01
Have a Google of `STUFF`, `FOR XML PATH` and `delimited string`. There are 100's of answers out there, all using the same logic. — Thom A, Dec 20 '17 at 10:03

score 2 · Accepted Answer · answered Dec 20 '17 at 10:05

Using STUFF function & FOR XML PAT as next:-

 STUFF((SELECT DISTINCT ', ' + technology
           FROM #tempTable b 
           WHERE b.id = a.id 
          FOR XML PATH('')), 1, 2, '')

Demo:-

create table #tempTable 
(
id int,
technology nvarchar(max)
)

insert into #tempTable values    
 (1 ,'MCA')
,(1 ,'BSC SCIENCE')
,(2 ,'BTECH')
,(3 ,'BTECH')
,(3 ,'MTECH')


SELECT id, displayname = 
    STUFF((SELECT DISTINCT ', ' + technology
           FROM #tempTable b 
           WHERE b.id = a.id 
          FOR XML PATH('')), 1, 2, '')
FROM #tempTable a
GROUP BY id

Result:-

1   BSC SCIENCE, MCA
2   BTECH
3   BTECH, MTECH

score 0 · Answer 2 · answered Dec 20 '17 at 10:16

declare @tab table(id int,  technology varchar(30))
insert into @tab values(1 ,   'MCA'),
(1,    'BSC SCIENCE'),
(2 ,   'BTECH')       ,  
(3 ,   'BTECH')        ,
(3 ,   'MTECH')  


SELECT t1.ID, 
    STUFF(
                 (SELECT ',' + technology FROM @tab t where t.id = t1.id FOR XML PATH ('')), 1, 1, ''
               ) 
FROM @tab t1 GROUP BY id

output

ID  (No column name)
1   MCA,BSC SCIENCE
2   BTECH
3   BTECH,MTECH

Group together the records by column

2 Answers2