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 22 Answers
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. Original 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], STUFF(( SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) FROM #YourTable WHERE (ID = Results.ID) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS NameValues FROM #YourTable Results GROUP BY ID DROP TABLE #YourTable If it is SQL Server 2017 or SQL Server Vnext, SQL Azure you can use string_agg as below:
select id, string_agg(concat(name, ':', [value]), ', ') from #YourTable group by id using XML path will not perfectly concatenate as you might expect... it will replace "&" with "&" and will also mess with <" and "> ...maybe a few other things, not sure...but you can try this
I came across a workaround for this... you need to replace:
FOR XML PATH('') ) with:
FOR XML PATH(''),TYPE ).value('(./text())[1]','VARCHAR(MAX)') ...or NVARCHAR(MAX) if thats what youre using.
why the hell doesn't SQL have a concatenate aggregate function? this is a PITA.
I ran into a couple of problems when I tried converting Kevin Fairchild's suggestion to work with strings containing spaces and special XML characters (&, <, >) which were encoded.
The final version of my code (which doesn't answer the original question but may be useful to someone) looks like this:
CREATE TABLE #YourTable ([ID] INT, [Name] VARCHAR(MAX), [Value] INT) INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'Oranges & Lemons',4) INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'1 < 2',8) INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9) SELECT [ID], STUFF(( SELECT ', ' + CAST([Name] AS VARCHAR(MAX)) FROM #YourTable WHERE (ID = Results.ID) FOR XML PATH(''),TYPE /* Use .value to uncomment XML entities e.g. > < etc*/ ).value('.','VARCHAR(MAX)') ,1,2,'') as NameValues FROM #YourTable Results GROUP BY ID DROP TABLE #YourTable Rather than using a space as a delimiter and replacing all the spaces with commas, it just pre-pends a comma and space to each value then uses STUFF to remove the first two characters.
The XML encoding is taken care of automatically by using the TYPE directive.
0Another 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 Install the SQLCLR Aggregates from
Then you can write code like this to get the result you asked for:
CREATE TABLE foo ( id INT, name CHAR(1), Value CHAR(1) ); INSERT INTO dbo.foo (id, name, Value) VALUES (1, 'A', '4'), (1, 'B', '8'), (2, 'C', '9'); SELECT id, dbo.GROUP_CONCAT(name + ':' + Value) AS [Column] FROM dbo.foo GROUP BY id; Eight years later... Microsoft SQL Server vNext Database Engine has finally enhanced Transact-SQL to directly support grouped string concatenation. The Community Technical Preview version 1.0 added the STRING_AGG function and CTP 1.1 added the WITHIN GROUP clause for the STRING_AGG function.
0SQL 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.
2An example would be
In Oracle you can use LISTAGG aggregate function.
Original records
name type ------------ name1 type1 name2 type2 name2 type3 Sql
SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name) FROM table GROUP BY name Result in
name type ------------ name1 type1 name2 type2; type3 This is just an addition to Kevin Fairchild's post (very clever by the way). I would have added it as a comment, but I don't have enough points yet :)
I was using this idea for a view I was working on, however the items I was concatinating contained spaces. So I modified the code slightly to not use spaces as delimiters.
Again thanks for the cool workaround Kevin!
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(REPLACE(REPLACE( (SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) as A FROM #YourTable WHERE ( ID = Results.ID ) FOR XML PATH ('')) , '</A><A>', ', ') ,'<A>','') ,'</A>','') AS NameValues FROM #YourTable Results GROUP BY ID DROP TABLE #YourTable This kind of question is asked here very often, and the solution is going to depend a lot on the underlying requirements:
and
Typically, there is no SQL-only way to do this without either dynamic sql, a user-defined function, or a cursor.
3Just to add to what Cade said, this is usually a front-end display thing and should therefore 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.
1Don'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 Let's get very simple:
SELECT stuff( ( select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb FOR XML PATH('') ) , 1, 2, '') Replace this line:
select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb With your query.
0You can improve performance significant the following way if group by contains mostly one item:
SELECT [ID], CASE WHEN MAX( [Name]) = MIN( [Name]) THEN MAX( [Name]) NameValues ELSE STUFF(( SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) FROM #YourTable WHERE (ID = Results.ID) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS NameValues END FROM #YourTable Results GROUP BY ID didn't see any cross apply answers, also no need for xml extraction. Here is a slightly different version of what Kevin Fairchild wrote. It's faster and easier to use in more complex queries:
select T.ID ,MAX(X.cl) NameValues from #YourTable T CROSS APPLY (select STUFF(( SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) FROM #YourTable WHERE (ID = T.ID) FOR XML PATH('')) ,1,2,'') [cl]) X GROUP BY T.ID Using the Stuff and for xml path operator to concatenate rows to string :Group By two columns -->
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 (1,'B',5) INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9) -- retrieve each unique id and name columns and concatonate the values into one column SELECT [ID], STUFF(( SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES EACH APPLICATION : VALUE SET FROM #YourTable WHERE (ID = Results.ID and Name = results.[name] ) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS NameValues FROM #YourTable Results GROUP BY ID SELECT [ID],[Name] , --these are acting as the group by clause STUFF(( SELECT ', '+ CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES THE VALUES FOR EACH ID NAME COMBINATION FROM #YourTable WHERE (ID = Results.ID and Name = results.[name] ) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS NameValues FROM #YourTable Results GROUP BY ID, name DROP TABLE #YourTable Using Replace Function and FOR JSON PATH
SELECT T3.DEPT, REPLACE(REPLACE(T3.ENAME,'{"ENAME":"',''),'"}','') AS ENAME_LIST FROM ( SELECT DEPT, (SELECT ENAME AS [ENAME] FROM EMPLOYEE T2 WHERE T2.DEPT=T1.DEPT FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) ENAME FROM EMPLOYEE T1 GROUP BY DEPT) T3 For sample data and more ways click here
If you have clr enabled you could use the Group_Concat library from GitHub
Another example without the garbage: ",TYPE).value('(./text())[1]','VARCHAR(MAX)')"
WITH t AS ( SELECT 1 n, 1 g, 1 v UNION ALL SELECT 2 n, 1 g, 2 v UNION ALL SELECT 3 n, 2 g, 3 v ) SELECT g , STUFF ( ( SELECT ', ' + CAST(v AS VARCHAR(MAX)) FROM t sub_t WHERE sub_t.g = main_t.g FOR XML PATH('') ) , 1, 2, '' ) cg FROM t main_t GROUP BY g Input-output is
************************* -> ********************* * n * g * v * * g * cg * * - * - * - * * - * - * * 1 * 1 * 1 * * 1 * 1, 2 * * 2 * 1 * 2 * * 2 * 3 * * 3 * 2 * 3 * ********************* ************************* I used this approach which may be easier to grasp. Get a root element, then concat to choices any item with the same ID but not the 'official' name
Declare @IdxList as Table(id int, choices varchar(max),AisName varchar(255)) Insert into @IdxLIst(id,choices,AisName) Select IdxId,''''+Max(Title)+'''',Max(Title) From [dbo].[dta_Alias] where IdxId is not null group by IdxId Update @IdxLIst set choices=choices +','''+Title+'''' From @IdxLIst JOIN [dta_Alias] ON id=IdxId And Title <> AisName where IdxId is not null Select * from @IdxList where choices like '%,%' For all my healthcare folks out there:
SELECT s.NOTE_ID ,STUFF (( SELECT [note_text] + ' ' FROM HNO_NOTE_TEXT s1 WHERE (s1.NOTE_ID = s.NOTE_ID) ORDER BY [line] ASC FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') , 1, 2, '') AS NOTE_TEXT_CONCATINATED FROM HNO_NOTE_TEXT s GROUP BY NOTE_ID 
