By Eldila


2008-11-07 19:08:00 8 Comments

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

17 comments

@Mahesh 2019-08-30 11:45:05

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

@Michal B. 2013-02-01 12:33:50

An 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

@user12861 2013-02-07 15:43:00

Looks nice, but the questions is specifically not about Oracle.

@Michal B. 2013-02-08 10:36:47

I understand. But I was looking for the same thing for Oracle, so I thought I would put it here for other people like me :)

@gregory 2017-02-10 21:52:39

@MichalB. Aren't you missing the within syntax? e.g: listagg(type, ', ') within group(order by name) ?

@Michal B. 2017-02-15 10:57:47

@gregory: I edited my answer. I think my old solution used to work back in the days. The current form that you suggested will work for sure, thanks.

@Mike M 2018-03-22 11:15:24

for future folks - you can write a new question with your own answer for a significant difference like different platform

@Jonathan Sayce 2011-10-18 10:48:44

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. &gt; &lt; 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.

@Kannan Kandasamy 2017-04-27 17:32:28

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

@Arkemlar 2017-06-15 09:46:37

Also works in postgres.

@asidis 2019-05-15 11:55:40

Should be the accepted answer now

@Velocitas 2019-09-16 15:02:31

This worked perfectly for what I need. I'm always so glad when I scroll down further and look for any possibly newer answers.

@Mordechai 2017-03-15 10:29:16

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

@vCillusion 2018-06-02 22:20:21

Without using Value, we can run into problems where the text is an XML encoded character

@Shem Sargent 2017-02-10 21:03:57

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.

Reference: https://msdn.microsoft.com/en-us/library/mt775028.aspx

@Orlando Colamatteo 2016-03-19 03:40:12

Install the SQLCLR Aggregates from http://groupconcat.codeplex.com

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;

@AFract 2016-09-13 08:24:29

I used it a few years ago, the syntax is much cleaner than all "XML Path" tricks and it works very well. I strongly recommend it when SQL CLR functions are an option.

@Marquinho Peli 2015-03-30 21:35:42

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.

@Eduard 2015-06-23 12:24:10

You 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

@jnm2 2016-06-30 14:35:52

Assuming you don't want duplicate names in the list, which you might or might not.

@Allen 2011-12-06 17:58:00

using XML path will not perfectly concatenate as you might expect... it will replace "&" with "&amp;" 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.

@MikeTeeVee 2013-03-15 19:01:13

I have scoured the net looking for the best way to NOT encode the output. Thank you SO much! This is the definitive answer - until MS adds proper support for this, like a CONCAT() aggregate function. What I do is throw this into an Outer-Apply that returns my concatenated field. I'm not a fan of adding nested-selects into my select-statements.

@vCillusion 2018-06-02 22:23:23

I agreed, without using Value, we can run into problems where the text is an XML encoded character. Please find my blog covering scenarios for grouped concatenation in SQL server. blog.vcillusion.co.in/…

@Kevin Fairchild 2008-11-07 19:31:35

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

@Amy B 2008-11-07 19:33:11

why would one nolock a temp table?

@user12861 2008-11-07 21:27:14

This is the coolest SQL thing I've seen in my life. Any idea if it's "fast" for large data sets? It doesn't start to crawl like a cursor would or anything, does it? I wish more people would vote this craziness up.

@Kevin Fairchild 2008-11-07 21:40:16

@mbrierst, this example was not made with efficiency in mind. Mainly just wanted to show an alternate solution. When you ask how fast it is for large data sets, what's your definition of 'large' and 'fast'? I'm sure there's a more elegant way to rewrite it with better efficiency, though.

@Cade Roux 2008-11-07 21:49:35

Very cool. Query plan indicates that it performs only two table scans and then a nested loop. udf probably cannot be optimized similarly. Don't normally use FOR XML much, I should learn more about its tricks.

@user12861 2008-11-07 21:58:12

I'm not at all sure there's a way to write it more efficiently, if anyone knows one please step up. And it's not less elegant than the hideous pivot statements that have been added to the language. I liked this so much I found some of your other answers and voted up the ones that I liked.

@Kevin Fairchild 2008-11-07 22:02:54

Eh. I just hate the sub-query style of it. JOINS are so much nicer. Just don't think I can utilize that in this solution. Anyhow, I'm glad to see there are other SQL dorks on here aside from me who like learning stuff like this. Kudos to you all :)

@user12861 2008-11-07 22:10:39

I hate subqueries too. Good point. But the craziness of FOR XML PATH has blinded me to everything else in your query. Anyway, on to the rest of my life. And I wish an SQL dork besides me would come work at my company for once.

@Jonathan Sayce 2011-10-18 10:54:17

A slightly cleaner way of doing the string manipulation: STUFF((SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) FROM #YourTable WHERE (ID = Results.ID) FOR XML PATH ('')),1,2,'') AS NameValues

@Shabi_669 2013-07-11 21:55:47

Wonderful and insane :-) I used a slightly different version to get all tables in a database: SELECT STUFF ( ( SELECT ', ' + [Name] FROM sys.objects WHERE [type] = 'U' FOR XML PATH ('') ) ,1 ,2 ,'' ) AS [Tables] FROM sys.objects WHERE [type] = 'U' GROUP BY [type]

@Jaloopa 2013-07-30 15:22:06

Just to note something I've found. Even in a case insensitive environment, the .value part of the query NEEDS to be lower case. I'm guessing this is because it's XML, which is case sensitive

@Jim Pedid 2014-02-26 23:09:13

I find it embarrassing for SQLServer that this is the only solution to this problem without using variables.

@Doug_Ivison 2014-08-22 19:17:03

Remember: make the subquery's WHERE include all columns in the GROUP BY that are necessary for the same degree of uniqueness. For example, if you have GROUP BY ID, ORDERNO, then you might need WHERE (ID = Results.ID) AND (ORDERNO = Results.ORDERNO)

@David Rogers 2017-04-20 22:22:20

This is really more a workaround than answer, what if you have a very large query with no easy ID column and many + joins + sub-queries + group by conditions in a view? Copy paste the entire query into the "FOR XML" clause(for each column you wish to join), is that really the best solution that SQL Server has to offer? I think the real answer is that until 2017 string concatenation has not been natively supported by SQL Server. Very disappointing :(

@Kevin Fairchild 2017-04-21 13:24:42

@DavidRogers, perhaps your queries need further simplified before you use this technique. In any event, I was glad to see that STRING_AGG WITHIN GROUP has been added to SQL 2017 and highly recommend that for people using that version or later down the road. Since that functionality wasn't available at the time this question was answered and SQL 2017 is still in Preview release and not in use by the majority of users trying to find a solution to this issue on Pre-2017 SQL Server, I stand by this as an 'answer' rather than a 'workaround'. :)

@Artur Kędzior 2019-06-13 09:22:35

Pure awesomness

@Tom H 2008-11-07 19:22:42

Just 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.

@MGOwen 2016-08-03 03:21:14

Grouping is a front-end display thing now? There are plenty of valid scenarios for concatenating one column in a grouped result set.

@Phillip 2011-05-09 16:12:18

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 

@cyberkiwi 2010-06-10 10:31:45

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

@gbdavid 2015-05-29 08:57:15

Thanks for the input, I always prefer using CTEs and Recursive CTEs to solve problems in SQL server. This is worked one works for me great!

@Fire in the Hole 2015-10-28 10:01:08

is it possible to use it in a query with outer apply?

@Joel Coehoorn 2008-11-20 04:11:23

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.

@user166390 2012-07-11 20:27:23

Unfortunately this requires (?) using CLR assemblies .. which is another issues to deal with :-/

@Shiv 2016-09-26 04:45:56

Just the example uses CLR for the actual concatenation implementation but this is not required. You could make the concatenation aggregate function use FOR XML so at least it's neater to call it in future!

@Amy B 2008-11-07 19:29:26

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

@Amy B 2015-03-10 02:19:21

@marc_s perhaps a better criticism is that PRIMARY KEY should be declared on the table variables.

@Amy B 2015-03-10 04:34:45

@marc_s On further inspection, that article is a sham - as are almost all discussions of performance without IO measurement. I did learn about LAG - so thanks for that.

@Cade Roux 2008-11-07 19:13:47

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

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

and

https://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.

@Björn Lindqvist 2013-07-25 11:24:04

Not true. cyberkiwi's solution using cte:s is pure sql without any vendor-specific hackery.

@Cade Roux 2013-07-25 15:11:32

At the time of the question and answer, I would not have counted recursive CTEs as terribly portable, but they are supported now by Oracle. The best solution is going to depend upon the platform. For SQL Server it is most likely the FOR XML technique or a customer CLR aggregate.

@Junchen Liu 2016-12-08 11:07:12

the ultimate answer for all questions? stackoverflow.com/search?q=[whatever the question]

Related Questions

Sponsored Content

47 Answered Questions

31 Answered Questions

[SOLVED] How to concatenate string variables in Bash

29 Answered Questions

37 Answered Questions

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

24 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

8 Answered Questions

[SOLVED] How does database indexing work?

24 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

Sponsored Content