grouping based on name

Question

How do i fetch the details of following table

table1:

c1    c2    
ta     a    
tb     c    
ta     b    
tb     d

by grouping with same name, like

ta    a,b
tb    c,d

which function to use in mssql

If 2017+ you are looking for string_agg() otherwise the XML approaches presented. — John Cappelletti, Feb 06 '20 at 14:45

score 3 · Accepted Answer · answered Feb 06 '20 at 14:41

3

You can use XML stuff combination

select distinct c1,
STUFF((Select ','+c2    
from table1 T1
where T1.c1 =T2.c1    
FOR XML PATH('')),1,1,'') from table1 T2

answered Feb 06 '20 at 14:41

zip

3,938
2
11
19

All the answers are correct, but i found this one very easy :p – geek Feb 07 '20 at 14:49

score 2 · Answer 2 · answered Feb 06 '20 at 14:45

Depends on your version of SQL Server; as of SQL 2017 you can use

Table

DECLARE @t TABLE (C1 VARCHAR(255), C2 VARCHAR(25))
INSERT INTO @t VALUES 
('ta','a'),('tb','c'),('ta','b'),('tb','d')

Query

SELECT C1, STRING_AGG(C2,',')
FROM    @t
GROUP BY C1

score 2 · Answer 3 · answered Feb 06 '20 at 14:49

Im late to the party but here ya go:

In 2017 string concatenation becomes super easy.

In older forms of SQL Server, you could use multiple techniques to get it done, like FOR XML (as show by another user), Unions, loops (which while gross is technically another way to do so) and of course the dark arts of recursive CTE's (which might be over kill for this example).

drop table #tmp;
create table #tmp
    (
        c1 varchar(2)
        , c2 varchar (2)
    );

INSERT INTO #tmp (c1, c2) VALUES
    ('ta', 'a'),
    ('tb', 'c'),
    ('ta', 'b'),
    ('tb', 'd');


SELECT c1, STRING_AGG(c2, ',') AS Concat
FROM #tmp
GROUP BY C1;

grouping based on name

3 Answers3