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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Using the Stuff and for xml path operator to concatenate rows to string  Group By two columns -- gt  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

User · Answer

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

User · Answer

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

User · Answer

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                                   lt  A gt  lt A gt                             lt A gt                  lt  A gt       AS NameValues  FROM    YourTable Results  GROUP  BY ID   DROP TABLE  YourTable

User · Answer

If you have clr enabled you could use the Group Concat library from GitHub

User · Answer

If you have clr enabled you could use the Group Concat library from GitHub

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

using XML path will not perfectly concatenate as you might expect    it will replace   amp   with   amp amp   and will also mess with  lt   and   gt     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

User · Answer

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   amp    lt    gt   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  amp  Lemons  4  INSERT INTO  YourTable   ID   Name   Value   VALUES  1  1  lt  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   amp gt   amp 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

User · Answer

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                                   lt  A gt  lt A gt                             lt A gt                  lt  A gt       AS NameValues  FROM    YourTable Results  GROUP  BY ID   DROP TABLE  YourTable

User · Answer

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

User · Answer

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

User · Answer

Using the Stuff and for xml path operator to concatenate rows to string  Group By two columns -- gt  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

User · Answer

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

User · Answer

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

User · Answer

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  lt  id  END  SELECT   FROM  Target

User · Answer

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  lt  id  END  SELECT   FROM  Target

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   amp    lt    gt   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  amp  Lemons  4  INSERT INTO  YourTable   ID   Name   Value   VALUES  1  1  lt  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   amp gt   amp 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

User · Answer

using XML path will not perfectly concatenate as you might expect    it will replace   amp   with   amp amp   and will also mess with  lt   and   gt     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

User · Answer

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

User · Answer

Another example without the garbage   quot  TYPE  value     text    1    VARCHAR MAX    quot  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                             - gt                             n       g       v               g       cg            -       -       -               -       -             1       1       1               1       1  2          2       1       2               2       3             3       2       3

User · Answer

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  lt  id  END  SELECT   FROM  Target

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  id  END  SELECT   FROM  Target

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Another example without the garbage   quot  TYPE  value     text    1    VARCHAR MAX    quot  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                             - gt                             n       g       v               g       cg            -       -       -               -       -             1       1       1               1       1  2          2       1       2               2       3             3       2       3

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to use GROUP BY to concatenate strings in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to string-concatenation

Examples related to sql-server-group-concat