CODEWITHSUNDEEP
sql-server
Predrag Stojadinović
Predrag Stojadinović

Reputation: 3659

SQL Server concatenation with GROUP BY

Suppose I have this table:

a   b   c
-----------
1   a   d
2   a   d
3   b   d
4   b   d
5   c   e
6   c   e

Is there a way that I could group by b and c and get a concatenated value for matching a's?

Like this:

a   b   c
-----------
1,2 a   d
3,3 b   d
5,6 c   e

I can get one a by using GROUP BY: 

SELECT MAX(a) AS a, b, c 
FROM mytable 
GROUP BY b, c

but that is not what I am looking for...

Upvotes: 0

Views: 72

Answers (3)

GuidoG
GuidoG

Reputation: 12014

Stuff is the best solution I agree.
Just to give you another option you can also do it without stuff

declare @t table (a int, b varchar(1), c varchar(1))
insert into @t values (1, 'a', 'd'), (2, 'a', 'd'), (3, 'b', 'd'), (4, 'b', 'd'), (5, 'c', 'e'), (6, 'c', 'e')

select left(temp.a, len(temp.a) - 1) as a, 
       temp.b, 
       temp.c
from   ( select t.b, 
                t.c,
                ( select convert(varchar, t2.a) + ',' AS [text()]
                  from   @t t2
                  where  t2.b = t.b
                  and    t2.c = t.c
                  For XML PATH ('')
                ) as a
         from   @t t
         group by t.b, t.c
       ) temp

this would give you the same result as stuff but is much more complex off course

Upvotes: 1

B3S
B3S

Reputation: 1051

Isnt this a simple STUFF concatenation?

DECLARE @MYTAB TABLE (ID INT,
                      ONE NVARCHAR(1),
                      TWO NVARCHAR(1)
                     )

INSERT INTO @MYTAB VALUES
(1, 'a', 'd'),
(2, 'a', 'd'),
(3, 'b', 'd'),
(4, 'b', 'd'),
(5, 'c', 'e'),
(6, 'c', 'e')


SELECT STUFF(
(
    SELECT N', '+CAST(ID AS NVARCHAR(50))
    FROM @MYTAB T2
    WHERE T2.ONE = T.ONE
          AND T2.TWO = T.TWO FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)'), 1, 2, N'') [MY AGGREGATE],
       ONE,
       TWO
FROM @MYTAB T
GROUP BY T.ONE,
         T.TWO;

SQL SERVER 2017:

SELECT  STRING_AGG(CAST(ID AS nvarchar(50)), ', '),
        ONE,
        TWO
FROM @MYTAB
GROUP BY ONE, TWO

Output:

AGG     ONE TWO
---------------
1, 2    a   d
3, 4    b   d
5, 6    c   e

Upvotes: 2

Surendra
Surendra

Reputation: 721

This should solve your problem 

create table #t 
(
    a char(1)
    ,b char(1)
    ,c char(1) 
)

insert into #t
select '1','a','d' union all 
select '2','a','d' union all 
select '3','b','d' union all 
select '4','b','d' union all 
select '5','c','e' union all 
select '6','c','e' 

select stuff ((select ','+ a FROM #t T1 where T1.b = T.B and T1.c = T.c For XML  PATH ('')) ,1,1,''),b,c
FROM #t T 
group by b,c

Upvotes: 1

Related Questions