How to use GROUP BY to concatenate strings in SQL Server?

Question

How do I get:

id       Name       Value
1          A          4
1          B          8
2          C          9

to

id          Column
1          A:4, B:8
2          C:9

Answer 1

I've looked for ways to do this before, and besides writing a stored proc, there isn't an easy way to do it. I resort to writing a script or using an ETL tool like Talend to do this.

Answer 2

This kind of question is asked here very often, and the solution is going to depend a lot on the underlying requirements:

http://stackoverflow.com/search?q=sql+pivot

and

http://stackoverflow.com/search?q=sql+concatenate

Typically, there is no SQL-only way to do this without either dynamic sql, a user-defined function, or a cursor.

Answer 3

This type of problem is solved easily on MySQL with its GROUP_CONCAT() aggregate function, but solving it on Microsoft SQL Server is more awkward.

See the following SO question for help: "How to get multiple records against one record based on relation?"

Answer 4

Just to add to what Cade said, this is usually a front-end display thing and should therefor be handled there. I know that sometimes it's easier to write something 100% in SQL for things like file export or other "SQL only" solutions, but most of the times this concatenation should be handled in your display layer.

Answer 5

Don't need a cursor... a while loop is sufficient.

------------------------------
-- Setup
------------------------------

DECLARE @Source TABLE
(
  id int,
  Name varchar(30),
  Value int
)

DECLARE @Target TABLE
(
  id int,
  Result varchar(max) 
)


INSERT INTO @Source(id, Name, Value) SELECT 1, 'A', 4
INSERT INTO @Source(id, Name, Value) SELECT 1, 'B', 8
INSERT INTO @Source(id, Name, Value) SELECT 2, 'C', 9


------------------------------
-- Technique
------------------------------

INSERT INTO @Target (id)
SELECT id
FROM @Source
GROUP BY id

DECLARE @id int, @Result varchar(max)
SET @id = (SELECT MIN(id) FROM @Target)

WHILE @id is not null
BEGIN
  SET @Result = null

  SELECT @Result =
    CASE
      WHEN @Result is null
      THEN ''
      ELSE @Result + ', '
    END + s.Name + ':' + convert(varchar(30),s.Value)
  FROM @Source s
  WHERE id = @id

  UPDATE @Target
  SET Result = @Result
  WHERE id = @id

  SET @id = (SELECT MIN(id) FROM @Target WHERE @id < id)
END

SELECT *
FROM @Target

Answer 6

No CURSOR, WHILE loop, or User-Defined Function needed.

Just need to be creative with FOR XML and PATH ;)

[Note: This solution only works on SQL 2005 and later. Originally question didn't specify the version in use.]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
      [ID]
    , REPLACE(RTRIM((SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) + ' ' FROM #YourTable WHERE (ID = Results.ID) FOR XML PATH (''))),' ',', ') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

UPDATE: I'm sure there's a better way to get around the string manipulation slight-of-hand going on there, but I can't think of it at the moment. If I use ', ' as the deliminator, it's going to end with a comma. My cheesy solution for this is to use a space as the deliminator. It'll still end with the deliminator (space), but RTRIM gets rid of it. Then I use REPLACE to change it from a single space to a comma and then a space. Seems a bit obtuse of a solution, but it works for the sample data :-P

Answer 7

SQL Server 2005 and later allow you to create your own custom aggregate functions, including for things like concatenation- see the sample at the bottom of the linked article.

Answer 8

Another option using Sql Server 2005 and above

---- test data
declare @t table (OUTPUTID int, SCHME varchar(10), DESCR varchar(10))
insert @t select 1125439       ,'CKT','Approved'
insert @t select 1125439       ,'RENO','Approved'
insert @t select 1134691       ,'CKT','Approved'
insert @t select 1134691       ,'RENO','Approved'
insert @t select 1134691       ,'pn','Approved'

---- actual query
;with cte(outputid,combined,rn)
as
(
  select outputid, SCHME + ' ('+DESCR+')', rn=ROW_NUMBER() over (PARTITION by outputid order by schme, descr)
  from @t
)
,cte2(outputid,finalstatus,rn)
as
(
select OUTPUTID, convert(varchar(max),combined), 1 from cte where rn=1
union all
select cte2.outputid, convert(varchar(max),cte2.finalstatus+', '+cte.combined), cte2.rn+1
from cte2
inner join cte on cte.OUTPUTID = cte2.outputid and cte.rn=cte2.rn+1
)
select outputid, MAX(finalstatus) from cte2 group by outputid