How to concatenate text from multiple rows into a single text string in SQL server

Question

Consider a database table holding names  with three rows   Peter Paul Mary   Is there an easy way to turn this into a single string of Peter  Paul  Mary

User · Answer

Use COALESCE - Learn more from here  For an example      102       103      104   Then write below code in sql server   Declare  Numbers AS Nvarchar MAX  -- It must not be MAX if you have few numbers  SELECT   Numbers   COALESCE  Numbers              Number FROM   TableName where Number IS NOT NULL  SELECT  Numbers   Output would be   102 103 104

User · Answer

One method not yet shown via the XML data   command in MS SQL Server is   Assume table called NameList with one column called FName   SELECT FName        AS  data     FROM NameList  FOR XML PATH       returns     Peter  Paul  Mary      Only the extra comma must be dealt with   Edit  As adopted from  NReilingh s comment  you can use the following method to remove the trailing comma   Assuming the same table and column names   STUFF REPLACE  SELECT        LTRIM RTRIM FName   AS  data    FROM NameList FOR XML PATH                   1  2      as Brands

User · Answer

If you are on SQL Server 2017 or Azure  see Mathieu Renda answer    I had a similar issue when I was trying to join two tables with one-to-many relationships  In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily   If there is a table called STUDENTS  SubjectID       StudentName ----------      ------------- 1               Mary 1               John 1               Sam 2               Alaina 2               Edward   Result I expected was   SubjectID       StudentName ----------      ------------- 1               Mary  John  Sam 2               Alaina  Edward   I used the following T-SQL   SELECT Main SubjectID         LEFT Main Students Len Main Students -1  As  Students  FROM               SELECT DISTINCT ST2 SubjectID                                 SELECT ST1 StudentName       AS  text                    FROM dbo Students ST1                 WHERE ST1 SubjectID   ST2 SubjectID                 ORDER BY ST1 SubjectID                 FOR XML PATH                     Students          FROM dbo Students ST2        Main    You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don t need to do a sub-query   SELECT DISTINCT ST2 SubjectID       SUBSTRING                        SELECT     ST1 StudentName  AS  text                FROM dbo Students ST1             WHERE ST1 SubjectID   ST2 SubjectID             ORDER BY ST1 SubjectID             FOR XML PATH                 2  1000   Students  FROM dbo Students ST2

User · Answer

For Oracle DBs  see this question  How can multiple rows be concatenated into one in Oracle without creating a stored procedure   The best answer appears to be by  Emmanuel  using the built-in LISTAGG   function  available in Oracle 11g Release 2 and later   SELECT question id     LISTAGG element id       WITHIN GROUP  ORDER BY element id  FROM YOUR TABLE  GROUP BY question id   as  user762952 pointed out  and according to Oracle s documentation http   www oracle-base com articles misc string-aggregation-techniques php  the WM CONCAT   function is also an option  It seems stable  but Oracle explicitly recommends against using it for any application SQL  so use at your own risk   Other than that  you will have to write your own function  the Oracle document above has a guide on how to do that

User · Answer

Although it s too late  and already has many solutions  Here is simple solution for MySQL   SELECT t1 id          GROUP CONCAT t1 id  ids  FROM table t1 JOIN table t2 ON  t1 id   t2 id   GROUP BY t1 id

User · Answer

Depends on your database vendor   MySQL has concat ws  MS SQL Server expects you to do it in your client application   Update   you could also do it in an external procedure or UDF  perhaps by using a cursor or calling out to CLR code

User · Answer

SQL Server 2017  and SQL Azure  STRING AGG Starting with the next version of SQL Server  we can finally concatenate across rows without having to resort to any variable or XML witchery  STRING AGG  Transact-SQL  Without grouping SELECT STRING AGG Name        AS Departments FROM HumanResources Department   With grouping   SELECT GroupName  STRING AGG Name        AS Departments FROM HumanResources Department GROUP BY GroupName   With grouping and sub-sorting SELECT GroupName  STRING AGG Name        WITHIN GROUP  ORDER BY Name ASC  AS Departments FROM HumanResources Department  GROUP BY GroupName

User · Answer

How about this      ISNULL SUBSTRING REPLACE  select     FName as  data    from NameList for xml path                   2  300        MyList    Where the  300  could be any width taking into account the max number of items you think will show up

User · Answer

This answer may return unexpected results For consistent results  use one of the FOR XML PATH methods detailed in other answers    Use COALESCE   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names               Name  FROM People   Just some explanation  since this answer seems to get relatively regular views     Coalesce is really just a helpful cheat that accomplishes two things     1  No need to initialize  Names with an empty string value    2  No need to strip off an extra separator at the end    The solution above will give incorrect results if a row has a NULL Name value  if there is a NULL  the NULL will make  Names NULL after that row  and the next row will start over as an empty string again   Easily fixed with one of two solutions    DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names               Name FROM People WHERE Name IS NOT NULL   or   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names                    ISNULL Name   N A   FROM People   Depending on what behavior you want  the first option just filters NULLs out  the second option keeps them in the list with a marker message  replace  N A  with whatever is appropriate for you

User · Answer

Use COALESCE - Learn more from here  For an example      102       103      104   Then write below code in sql server   Declare  Numbers AS Nvarchar MAX  -- It must not be MAX if you have few numbers  SELECT   Numbers   COALESCE  Numbers              Number FROM   TableName where Number IS NOT NULL  SELECT  Numbers   Output would be   102 103 104

User · Answer

In SQL Server 2005  SELECT Stuff     SELECT N       Name FROM Names FOR XML PATH     TYPE     value  text   1    nvarchar max    1 2 N        In SQL Server 2016  you can use the FOR JSON syntax  i e    SELECT per ID  Emails   JSON VALUE     REPLACE        SELECT     em Email FROM Email em WHERE em Person   per ID FOR JSON PATH                            0        FROM Person per   And the result will become  Id  Emails 1   abc gmail com 2   NULL 3   def gmail com  xyz gmail com   This will work even your data contains invalid XML characters  the             is safe because if you data contain              it will be escaped to               You can replace      with any string separator    And in SQL Server 2017  Azure SQL Database  You can use the new STRING AGG function

User · Answer

I really liked elegancy of Dana s answer  Just wanted to make it complete   DECLARE  names VARCHAR MAX  SET  names       SELECT  names    names          Name FROM Names   -- Deleting last two symbols        SET  sSql   LEFT  sSql  LEN  sSql  - 1

User · Answer

in Postgres - array agg SELECT array to string array agg DISTINCT rolname        FROM pg catalog pg roles   OR STRING AGG SELECT STRING AGG rolname  text      FROM pg catalog pg roles

User · Answer

This answer may return unexpected results For consistent results  use one of the FOR XML PATH methods detailed in other answers    Use COALESCE   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names               Name  FROM People   Just some explanation  since this answer seems to get relatively regular views     Coalesce is really just a helpful cheat that accomplishes two things     1  No need to initialize  Names with an empty string value    2  No need to strip off an extra separator at the end    The solution above will give incorrect results if a row has a NULL Name value  if there is a NULL  the NULL will make  Names NULL after that row  and the next row will start over as an empty string again   Easily fixed with one of two solutions    DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names               Name FROM People WHERE Name IS NOT NULL   or   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names                    ISNULL Name   N A   FROM People   Depending on what behavior you want  the first option just filters NULLs out  the second option keeps them in the list with a marker message  replace  N A  with whatever is appropriate for you

User · Answer

DECLARE  Names VARCHAR 8000  SELECT  name      SELECT  Names    Names         Names FROM People SELECT SUBSTRING 2   Names  7998    This puts the stray comma at the beginning   However  if you need other columns  or to CSV a child table you need to wrap this in a scalar user defined field  UDF    You can use XML path as a correlated subquery in the SELECT clause too  but I d have to wait until I go back to work because Google doesn t do work stuff at home  -

User · Answer

One way you could do it in SQL nbsp Server would be to return the table content as XML  for XML raw   convert the result to a string and then replace the tags with

User · Answer

I usually use select like this to concatenate strings in SQL Server   with lines as       select      row number   over order by id  id  -- id is a line id     line -- line of text    from     source -- line source     result lines as       select      id       cast line as nvarchar max   line    from      lines    where      id   1    union all    select      l id       cast r line   N       l line as nvarchar max     from      lines l      inner join      result lines r      on        l id   r id   1     select top 1    line from   result lines order by   id desc

User · Answer

Using XML helped me in getting rows separated with commas  For the extra comma we can use the replace function of SQL nbsp Server  Instead of adding a comma  use of the AS  data    will concatenate the rows with spaces  which later can be replaced with commas as the syntax written below   REPLACE           select FName AS  data     from NameList  for xml path

User · Answer

on top of Chris Shaffer answer  if your data may get repeated Such as  Tom Ali John Ali Tom Mike   Instead of having Tom Ali John Ali Tom Mike  You can use DISTINCT to avoid duplicates and get Tom Ali John Mike  DECLARE  Names VARCHAR 8000   SELECT DISTINCT  Names   COALESCE  Names              Name FROM People WHERE Name IS NOT NULL SELECT  Names

User · Answer

DECLARE  Names VARCHAR 8000  SELECT  name      SELECT  Names    Names         Names FROM People SELECT SUBSTRING 2   Names  7998    This puts the stray comma at the beginning   However  if you need other columns  or to CSV a child table you need to wrap this in a scalar user defined field  UDF    You can use XML path as a correlated subquery in the SELECT clause too  but I d have to wait until I go back to work because Google doesn t do work stuff at home  -

User · Answer

This method applies to Teradata Aster database only as it utilizes its NPATH function    Again  we have table Students  SubjectID       StudentName ----------      ------------- 1               Mary 1               John 1               Sam 2               Alaina 2               Edward   Then with NPATH it is just single SELECT   SELECT   FROM npath    ON Students   PARTITION BY SubjectID   ORDER BY StudentName   MODE nonoverlapping    PATTERN  A      SYMBOLS       true  as A       RESULT      FIRST SubjectID of A  as SubjectID      ACCUMULATE StudentName of A  as StudentName          Result   SubjectID       StudentName ----------      ------------- 1                John  Mary  Sam  2                Alaina  Edward

User · Answer

Oracle 11g Release 2 supports the LISTAGG function   Documentation here   COLUMN employees FORMAT A50  SELECT deptno  LISTAGG ename       WITHIN GROUP  ORDER BY ename  AS employees FROM   emp GROUP BY deptno       DEPTNO EMPLOYEES ---------- --------------------------------------------------         10 CLARK KING MILLER         20 ADAMS FORD JONES SCOTT SMITH         30 ALLEN BLAKE JAMES MARTIN TURNER WARD  3 rows selected    Warning  Be careful implementing this function if there is possibility of the resulting string going over 4000 characters   It will throw an exception   If that s the case then you need to either handle the exception or roll your own function that prevents the joined string from going over 4000 characters

User · Answer

Not that I have done any analysis on performance as my list had less than 10 items but I was amazed after looking thru the 30 odd answers I still had a twist on a similar answer already given similar to using COALESCE for a single group list and didn t even have to set my variable  defaults to NULL anyhow  and it assumes all entries in my source data table are non blank   DECLARE  MyList VARCHAR 1000    Delimiter CHAR 2         SELECT  MyList   CASE WHEN  MyList  gt     THEN  MyList    Delimiter ELSE    END   FieldToConcatenate FROM MyData   I am sure COALESCE internally uses the same idea  Lets hope MS don t change this on me

User · Answer

Although it s too late  and already has many solutions  Here is simple solution for MySQL   SELECT t1 id          GROUP CONCAT t1 id  ids  FROM table t1 JOIN table t2 ON  t1 id   t2 id   GROUP BY t1 id

User · Answer

Not that I have done any analysis on performance as my list had less than 10 items but I was amazed after looking thru the 30 odd answers I still had a twist on a similar answer already given similar to using COALESCE for a single group list and didn t even have to set my variable  defaults to NULL anyhow  and it assumes all entries in my source data table are non blank   DECLARE  MyList VARCHAR 1000    Delimiter CHAR 2         SELECT  MyList   CASE WHEN  MyList  gt     THEN  MyList    Delimiter ELSE    END   FieldToConcatenate FROM MyData   I am sure COALESCE internally uses the same idea  Lets hope MS don t change this on me

User · Answer

MySQL complete Example   We have Users which can have many Data s and we want to have an output  where we can see all users Datas in a list   Result                                 id      rowList            -------------------------    0      6  9                 1      1 2 3 4 5 7 8 1                                  Table Setup   CREATE TABLE  Data       id  int 11  NOT NULL     user id  int 11  NOT NULL   ENGINE InnoDB AUTO INCREMENT 11 DEFAULT CHARSET latin1    INSERT INTO  Data    id    user id   VALUES  1  1    2  1    3  1    4  1    5  1    6  0    7  1    8  1    9  0    10  1     CREATE TABLE  User       id  int 11  NOT NULL   ENGINE InnoDB DEFAULT CHARSET latin1    INSERT INTO  User    id   VALUES  0    1     Query    SELECT User id  GROUP CONCAT Data id ORDER BY Data id  AS rowList FROM User LEFT JOIN Data ON User id   Data user id GROUP BY User id

User · Answer

Below is a simple PL SQL procedure to implement the given scenario using  basic loop  and  rownum   Table definition  CREATE TABLE  NAMES    NAME  VARCHAR2 10 BYTE        Let s insert values into this table  INSERT INTO NAMES VALUES  PETER    INSERT INTO NAMES VALUES  PAUL    INSERT INTO NAMES VALUES  MARY      Procedure starts from here  DECLARE   MAXNUM INTEGER  CNTR INTEGER    1  C NAME NAMES NAME TYPE  NSTR VARCHAR2 50    BEGIN  SELECT MAX ROWNUM  INTO MAXNUM FROM NAMES   LOOP  SELECT NAME INTO  C NAME FROM   SELECT ROWNUM RW  NAME FROM NAMES   P WHERE P RW   CNTR   NSTR    NSTR        C NAME  CNTR    CNTR   1  EXIT WHEN CNTR  gt  MAXNUM   END LOOP   dbms output put line SUBSTR NSTR 2     END    Result  PETER PAUL MARY

User · Answer

User1460901 You can try something like this   WITH cte base AS       SELECT CustomerCode  CustomerName      CASE WHEN Typez    Breakfast  THEN Items ELSE NULL END AS  BREAKFAST        CASE WHEN Typez    Lunch  THEN Items ELSE NULL END AS  LUNCH      FROM  Customer           SELECT distinct CustomerCode  CustomerName      SUBSTRING                   SELECT     BREAKFAST AS  text            FROM cte base b1         WHERE b1 CustomerCode   b2 CustomerCode AND b1 CustomerName   b2 CustomerName         ORDER BY b1 BREAKFAST         FOR XML PATH                2  1000        BREAKFAST        SUBSTRING                   SELECT     LUNCH AS  text            FROM cte base b1         WHERE b1 CustomerCode   b2 CustomerCode AND b1 CustomerName   b2 CustomerName         ORDER BY b1 LUNCH         FOR XML PATH                2  1000        LUNCH      FROM cte base b2

User · Answer

This answer may return unexpected results For consistent results  use one of the FOR XML PATH methods detailed in other answers    Use COALESCE   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names               Name  FROM People   Just some explanation  since this answer seems to get relatively regular views     Coalesce is really just a helpful cheat that accomplishes two things     1  No need to initialize  Names with an empty string value    2  No need to strip off an extra separator at the end    The solution above will give incorrect results if a row has a NULL Name value  if there is a NULL  the NULL will make  Names NULL after that row  and the next row will start over as an empty string again   Easily fixed with one of two solutions    DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names               Name FROM People WHERE Name IS NOT NULL   or   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names                    ISNULL Name   N A   FROM People   Depending on what behavior you want  the first option just filters NULLs out  the second option keeps them in the list with a marker message  replace  N A  with whatever is appropriate for you

User · Answer

I really liked elegancy of Dana s answer  Just wanted to make it complete   DECLARE  names VARCHAR MAX  SET  names       SELECT  names    names          Name FROM Names   -- Deleting last two symbols        SET  sSql   LEFT  sSql  LEN  sSql  - 1

User · Answer

declare  phone varchar max         select  phone  phone   mobileno      from  members    select  phone

User · Answer

First of all you should declare a table variable and fill it with your table data and after that  with a WHILE loop  select row one by one and add its value to a nvarchar max  variable      Go     declare  temp table          title nvarchar 50            insert into  temp title      select p Title from dbo person p     --     declare  mainString nvarchar max      set  mainString           --     while   select count    from  temp     0      begin         declare  itemTitle nvarchar 50          set  itemTitle    select top 1  t Title from  temp t               if  mainString              begin             set  mainString    itemTitle         end         else         begin             set  mainString   concat  mainString      itemTitle          end              delete top 1  from  temp          end     print  mainString

User · Answer

In Oracle  it is wm concat  I believe this function is available in the 10g release and higher

User · Answer

This answer may return unexpected results For consistent results  use one of the FOR XML PATH methods detailed in other answers    Use COALESCE   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names               Name  FROM People   Just some explanation  since this answer seems to get relatively regular views     Coalesce is really just a helpful cheat that accomplishes two things     1  No need to initialize  Names with an empty string value    2  No need to strip off an extra separator at the end    The solution above will give incorrect results if a row has a NULL Name value  if there is a NULL  the NULL will make  Names NULL after that row  and the next row will start over as an empty string again   Easily fixed with one of two solutions    DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names               Name FROM People WHERE Name IS NOT NULL   or   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE  Names                    ISNULL Name   N A   FROM People   Depending on what behavior you want  the first option just filters NULLs out  the second option keeps them in the list with a marker message  replace  N A  with whatever is appropriate for you

User · Answer

Depends on your database vendor   MySQL has concat ws  MS SQL Server expects you to do it in your client application   Update   you could also do it in an external procedure or UDF  perhaps by using a cursor or calling out to CLR code

User · Answer

I don t have access to a SQL Server at home  so I m guess at the syntax here  but it s more or less   DECLARE  names VARCHAR 500   SELECT  names    names         Name FROM Names

User · Answer

SELECT PageContent   Stuff          SELECT PageContent         FROM dbo InfoGuide         WHERE CategoryId    CategoryId           AND SubCategoryId    SubCategoryId         for xml path      type       value    1    nvarchar max         1  1      FROM dbo InfoGuide info

User · Answer

in Postgres - array agg SELECT array to string array agg DISTINCT rolname        FROM pg catalog pg roles   OR STRING AGG SELECT STRING AGG rolname  text      FROM pg catalog pg roles

User · Answer

A recursive CTE solution was suggested  but no code was provided   The code below is an example of a recursive CTE   Note that although the results match the question  the data doesn t quite match the given description  as I assume that you really want to be doing this on groups of rows  not all rows in the table   Changing it to match all rows in the table is left as an exercise for the reader    WITH basetable AS       SELECT         id          CAST name AS VARCHAR MAX   name           ROW NUMBER   OVER  Partition BY id ORDER BY seq  rw           COUNT    OVER  Partition BY id  recs      FROM  VALUES          1   Johnny   1            1   M   2             2   Bill   1            2   S    4            2   Preston   5            2   Esq    6            3   Ted   1            3   Theodore   2            3   Logan   3            4   Peter   1            4   Paul   2            4   Mary   3        g  id  name  seq     rCTE AS       SELECT recs  id  name  rw     FROM basetable     WHERE rw   1      UNION ALL      SELECT b recs  r ID  r name        b name name  r rw   1     FROM basetable b     INNER JOIN rCTE r ON b id   r id AND b rw   r rw   1   SELECT name FROM rCTE WHERE recs   rw AND ID 4

User · Answer

SELECT STUFF  SELECT        name FROM  table  FOR XML PATH       1  2        Here s a sample   DECLARE  t TABLE  name VARCHAR 10   INSERT INTO  t VALUES   Peter      Paul      Mary   SELECT STUFF  SELECT        name FROM  t FOR XML PATH       1  2      --Peter  Paul  Mary

User · Answer

declare  phone varchar max         select  phone  phone   mobileno      from  members    select  phone

User · Answer

There are couple ways in oracle       create table name      first name varchar2 30         insert into name values   Peter        insert into name values   Paul        insert into name values   Mary      Solution is 1       select substr max sys connect by path  first name        2  from  select rownum r  first name from name   n start with r 1 connect by prior r 1 r     o p  gt  Peter Paul Mary   Soution is 2       select  rtrim xmlagg  xmlelement  e  first name          extract     text           first name from name     o p  gt  Peter Paul Mary

User · Answer

In MySQL  there is a function  GROUP CONCAT    which allows you to concatenate the values from multiple rows  Example  SELECT 1 AS a  GROUP CONCAT name ORDER BY name ASC SEPARATOR       AS people  FROM users  WHERE id IN  1 2 3   GROUP BY a

User · Answer

A ready-to-use solution  with no extra commas   select substring           select      Name AS  data    from Names for xml path              3  255  as  MyList    An empty list will result in NULL value  Usually you will insert the list into a table column or program variable  adjust the 255 max length to your need    Diwakar and Jens Frandsen provided good answers  but need improvement

User · Answer

With the other answers  the person reading the answer must be aware of a specific domain table such as vehicle or student  The table must be created and populated with data to test a solution   Below is an example that uses SQL Server  Information Schema Columns  table  By using this solution  no tables need to be created or data added  This example creates a comma separated list of column names for all tables in the database   SELECT     Table Name      STUFF           SELECT       Column Name         FROM INFORMATION SCHEMA Columns Columns         WHERE Tables Table Name   Columns Table Name         ORDER BY Column Name         FOR XML PATH        1  1          Columns FROM INFORMATION SCHEMA Columns Tables GROUP BY TABLE NAME

User · Answer

This method applies to Teradata Aster database only as it utilizes its NPATH function    Again  we have table Students  SubjectID       StudentName ----------      ------------- 1               Mary 1               John 1               Sam 2               Alaina 2               Edward   Then with NPATH it is just single SELECT   SELECT   FROM npath    ON Students   PARTITION BY SubjectID   ORDER BY StudentName   MODE nonoverlapping    PATTERN  A      SYMBOLS       true  as A       RESULT      FIRST SubjectID of A  as SubjectID      ACCUMULATE StudentName of A  as StudentName          Result   SubjectID       StudentName ----------      ------------- 1                John  Mary  Sam  2                Alaina  Edward

User · Answer

With a recursive query you can do it   -- Create example table CREATE TABLE tmptable  NAME VARCHAR 30      -- Insert example data INSERT INTO tmptable VALUES  PETER    INSERT INTO tmptable VALUES  PAUL    INSERT INTO tmptable VALUES  MARY     -- Recurse query with tblwithrank as   select     row number   over order by name  rang   count    over   NbRow from tmptable    tmpRecursive as   select    cast name as varchar 2000   as AllName from tblwithrank  where rang 1 union all select f0     cast f0 name         f1 AllName as varchar 2000   as AllName  from tblwithrank f0 inner join tmpRecursive f1 on f0 rang f1 rang  1    select AllName from tmpRecursive where rang NbRow

User · Answer

We can use RECUSRSIVITY  WITH CTE  union ALL as follows  declare  mytable as table id int identity 1 1   str nvarchar 100   insert into  mytable values  Peter     Paul     Mary    declare  myresult as table id int str nvarchar max  ind int  R  int    with cte as select id cast str as nvarchar 100   as str  cast 0 as int  ind from  mytable union all select t2 id cast t1 str      t2 str as nvarchar 100    t1 ind 1 from cte t1 inner join  mytable t2 on t2 id t1 id 1  insert into  myresult select   row number   over order by ind  R  from cte  select top 1 str from  myresult order by R  desc

User · Answer

MySQL complete Example   We have Users which can have many Data s and we want to have an output  where we can see all users Datas in a list   Result                                 id      rowList            -------------------------    0      6  9                 1      1 2 3 4 5 7 8 1                                  Table Setup   CREATE TABLE  Data       id  int 11  NOT NULL     user id  int 11  NOT NULL   ENGINE InnoDB AUTO INCREMENT 11 DEFAULT CHARSET latin1    INSERT INTO  Data    id    user id   VALUES  1  1    2  1    3  1    4  1    5  1    6  0    7  1    8  1    9  0    10  1     CREATE TABLE  User       id  int 11  NOT NULL   ENGINE InnoDB DEFAULT CHARSET latin1    INSERT INTO  User    id   VALUES  0    1     Query    SELECT User id  GROUP CONCAT Data id ORDER BY Data id  AS rowList FROM User LEFT JOIN Data ON User id   Data user id GROUP BY User id

User · Answer

I don t have access to a SQL Server at home  so I m guess at the syntax here  but it s more or less   DECLARE  names VARCHAR 500   SELECT  names    names         Name FROM Names

User · Answer

To avoid null values you can use CONCAT    DECLARE  names VARCHAR 500  SELECT  names   CONCAT  names       name   FROM Names select  names

User · Answer

--SQL Server 2005   CREATE TABLE dbo Students       StudentId INT       Name VARCHAR 50        CONSTRAINT PK Students PRIMARY KEY  StudentId      CREATE TABLE dbo Subjects       SubjectId INT       Name VARCHAR 50        CONSTRAINT PK Subjects PRIMARY KEY  SubjectId      CREATE TABLE dbo Schedules       StudentId INT       SubjectId INT       CONSTRAINT PK  Schedule PRIMARY KEY  StudentId  SubjectId        CONSTRAINT FK Schedule Students FOREIGN KEY  StudentId  REFERENCES dbo Students  StudentId        CONSTRAINT FK Schedule Subjects FOREIGN KEY  SubjectId  REFERENCES dbo Subjects  SubjectId      INSERT dbo Students  StudentId  Name  VALUES      1   Mary          2   John          3   Sam          4   Alaina          5   Edward      INSERT dbo Subjects  SubjectId  Name  VALUES      1   Physics          2   Geography          3   French          4   Gymnastics      INSERT dbo Schedules  StudentId  SubjectId  VALUES      1  1       --Mary  Physics        2  1     --John  Physics        3  1     --Sam  Physics        4  2     --Alaina  Geography        5  2     --Edward  Geography    SELECT      sub SubjectId       sub Name AS  SubjectName        ISNULL  x Students      AS Students FROM     dbo Subjects sub     OUTER APPLY               SELECT              CASE ROW NUMBER   OVER  ORDER BY stu Name  WHEN 1 THEN    ELSE      END               stu Name         FROM             dbo Students stu             INNER JOIN dbo Schedules sch                 ON stu StudentId   sch StudentId         WHERE             sch SubjectId   sub SubjectId         ORDER BY             stu Name         FOR XML PATH           x  Students

User · Answer

To avoid null values you can use CONCAT    DECLARE  names VARCHAR 500  SELECT  names   CONCAT  names       name   FROM Names select  names

User · Answer

I don t have access to a SQL Server at home  so I m guess at the syntax here  but it s more or less   DECLARE  names VARCHAR 500   SELECT  names    names         Name FROM Names

User · Answer

Postgres arrays are awesome  Example   Create some test data   postgres    c test You are now connected to database  test  as user  hgimenez   test   create table names  name text   CREATE TABLE                                       test   insert into names  name  values   Peter      Paul      Mary                                                              INSERT 0 3 test   select   from names   name   -------  Peter  Paul  Mary  3 rows    Aggregate them in an array   test   select array agg name  from names   array agg      -------------------    Peter Paul Mary   1 row    Convert the array to a comma delimited string   test   select array to string array agg name         from names   array to string -------------------  Peter  Paul  Mary  1 row    DONE  Since PostgreSQL 9 0 it is even easier

User · Answer

User1460901 You can try something like this   WITH cte base AS       SELECT CustomerCode  CustomerName      CASE WHEN Typez    Breakfast  THEN Items ELSE NULL END AS  BREAKFAST        CASE WHEN Typez    Lunch  THEN Items ELSE NULL END AS  LUNCH      FROM  Customer           SELECT distinct CustomerCode  CustomerName      SUBSTRING                   SELECT     BREAKFAST AS  text            FROM cte base b1         WHERE b1 CustomerCode   b2 CustomerCode AND b1 CustomerName   b2 CustomerName         ORDER BY b1 BREAKFAST         FOR XML PATH                2  1000        BREAKFAST        SUBSTRING                   SELECT     LUNCH AS  text            FROM cte base b1         WHERE b1 CustomerCode   b2 CustomerCode AND b1 CustomerName   b2 CustomerName         ORDER BY b1 LUNCH         FOR XML PATH                2  1000        LUNCH      FROM cte base b2

User · Answer

SELECT STUFF  SELECT        name FROM  table  FOR XML PATH       1  2        Here s a sample   DECLARE  t TABLE  name VARCHAR 10   INSERT INTO  t VALUES   Peter      Paul      Mary   SELECT STUFF  SELECT        name FROM  t FOR XML PATH       1  2      --Peter  Paul  Mary

User · Answer

One way you could do it in SQL nbsp Server would be to return the table content as XML  for XML raw   convert the result to a string and then replace the tags with

User · Answer

SQL Server 2017  and SQL Azure  STRING AGG Starting with the next version of SQL Server  we can finally concatenate across rows without having to resort to any variable or XML witchery  STRING AGG  Transact-SQL  Without grouping SELECT STRING AGG Name        AS Departments FROM HumanResources Department   With grouping   SELECT GroupName  STRING AGG Name        AS Departments FROM HumanResources Department GROUP BY GroupName   With grouping and sub-sorting SELECT GroupName  STRING AGG Name        WITHIN GROUP  ORDER BY Name ASC  AS Departments FROM HumanResources Department  GROUP BY GroupName

User · Answer

First of all you should declare a table variable and fill it with your table data and after that  with a WHILE loop  select row one by one and add its value to a nvarchar max  variable      Go     declare  temp table          title nvarchar 50            insert into  temp title      select p Title from dbo person p     --     declare  mainString nvarchar max      set  mainString           --     while   select count    from  temp     0      begin         declare  itemTitle nvarchar 50          set  itemTitle    select top 1  t Title from  temp t               if  mainString              begin             set  mainString    itemTitle         end         else         begin             set  mainString   concat  mainString      itemTitle          end              delete top 1  from  temp          end     print  mainString

User · Answer

Depends on your database vendor   MySQL has concat ws  MS SQL Server expects you to do it in your client application   Update   you could also do it in an external procedure or UDF  perhaps by using a cursor or calling out to CLR code

User · Answer

This worked for me  SqlServer 2016    SELECT CarNamesString   STUFF            SELECT        Name              FROM tbl cars              FOR XML PATH                 1  1        Here is the source    https   www mytecbits com   And a solution for MySql  since this page show up in Google for MySql   SELECT  Name          GROUP CONCAT DISTINCT  Name   SEPARATOR             FROM tbl cars   From MySql Documentations

User · Answer

In SQL Server 2005 and later  use the query below to concatenate the rows   DECLARE  t table       Id int      Name varchar 10    INSERT INTO  t SELECT 1  a  UNION ALL SELECT 1  b  UNION ALL SELECT 2  c  UNION ALL SELECT 2  d    SELECT ID  stuff        SELECT       Name  FROM  t WHERE Id   t Id FOR XML PATH       1 1      FROM  SELECT DISTINCT ID FROM  t   t

User · Answer

In SQL Server vNext this will be built in with the STRING AGG function  read more about it here  https   msdn microsoft com en-us library mt790580 aspx

User · Answer

With TABLE type it is extremely easy  Let s imagine that your table is called Students and it has column name   declare  rowsCount INT declare  i INT   1 declare  names varchar max        DECLARE  MyTable TABLE     Id int identity    Name varchar 500    insert into  MyTable select name from Students set  rowsCount    select COUNT Id  from  MyTable   while  i  lt   rowsCount begin  set  names    names           select name from  MyTable where Id    i   set  i    i   1 end select  names   This example is tested in MS SQL Server 2008 R2

User · Answer

For Oracle DBs  see this question  How can multiple rows be concatenated into one in Oracle without creating a stored procedure   The best answer appears to be by  Emmanuel  using the built-in LISTAGG   function  available in Oracle 11g Release 2 and later   SELECT question id     LISTAGG element id       WITHIN GROUP  ORDER BY element id  FROM YOUR TABLE  GROUP BY question id   as  user762952 pointed out  and according to Oracle s documentation http   www oracle-base com articles misc string-aggregation-techniques php  the WM CONCAT   function is also an option  It seems stable  but Oracle explicitly recommends against using it for any application SQL  so use at your own risk   Other than that  you will have to write your own function  the Oracle document above has a guide on how to do that

User · Answer

We can use RECUSRSIVITY  WITH CTE  union ALL as follows  declare  mytable as table id int identity 1 1   str nvarchar 100   insert into  mytable values  Peter     Paul     Mary    declare  myresult as table id int str nvarchar max  ind int  R  int    with cte as select id cast str as nvarchar 100   as str  cast 0 as int  ind from  mytable union all select t2 id cast t1 str      t2 str as nvarchar 100    t1 ind 1 from cte t1 inner join  mytable t2 on t2 id t1 id 1  insert into  myresult select   row number   over order by ind  R  from cte  select top 1 str from  myresult order by R  desc

User · Answer

There are couple ways in oracle       create table name      first name varchar2 30         insert into name values   Peter        insert into name values   Paul        insert into name values   Mary      Solution is 1       select substr max sys connect by path  first name        2  from  select rownum r  first name from name   n start with r 1 connect by prior r 1 r     o p  gt  Peter Paul Mary   Soution is 2       select  rtrim xmlagg  xmlelement  e  first name          extract     text           first name from name     o p  gt  Peter Paul Mary

User · Answer

One method not yet shown via the XML data   command in MS SQL Server is   Assume table called NameList with one column called FName   SELECT FName        AS  data     FROM NameList  FOR XML PATH       returns     Peter  Paul  Mary      Only the extra comma must be dealt with   Edit  As adopted from  NReilingh s comment  you can use the following method to remove the trailing comma   Assuming the same table and column names   STUFF REPLACE  SELECT        LTRIM RTRIM FName   AS  data    FROM NameList FOR XML PATH                   1  2      as Brands

User · Answer

One way you could do it in SQL nbsp Server would be to return the table content as XML  for XML raw   convert the result to a string and then replace the tags with

User · Answer

You need to create a variable that will hold your final result and select into it  like so   Easiest Solution  DECLARE  char VARCHAR MAX    SELECT  char   COALESCE  char           column    column    FROM  table    PRINT  char

User · Answer

DECLARE  Names VARCHAR 8000  SELECT  name      SELECT  Names    Names         Names FROM People SELECT SUBSTRING 2   Names  7998    This puts the stray comma at the beginning   However  if you need other columns  or to CSV a child table you need to wrap this in a scalar user defined field  UDF    You can use XML path as a correlated subquery in the SELECT clause too  but I d have to wait until I go back to work because Google doesn t do work stuff at home  -

User · Answer

In SQL Server 2005 and later  use the query below to concatenate the rows   DECLARE  t table       Id int      Name varchar 10    INSERT INTO  t SELECT 1  a  UNION ALL SELECT 1  b  UNION ALL SELECT 2  c  UNION ALL SELECT 2  d    SELECT ID  stuff        SELECT       Name  FROM  t WHERE Id   t Id FOR XML PATH       1 1      FROM  SELECT DISTINCT ID FROM  t   t

User · Answer

If you want to deal with nulls you can do it by adding a where clause or add another COALESCE around the first one   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE COALESCE  Names               Name   Names  FROM People

User · Answer

I usually use select like this to concatenate strings in SQL Server   with lines as       select      row number   over order by id  id  -- id is a line id     line -- line of text    from     source -- line source     result lines as       select      id       cast line as nvarchar max   line    from      lines    where      id   1    union all    select      l id       cast r line   N       l line as nvarchar max     from      lines l      inner join      result lines r      on        l id   r id   1     select top 1    line from   result lines order by   id desc

User · Answer

You need to create a variable that will hold your final result and select into it  like so   Easiest Solution  DECLARE  char VARCHAR MAX    SELECT  char   COALESCE  char           column    column    FROM  table    PRINT  char

User · Answer

Using XML helped me in getting rows separated with commas  For the extra comma we can use the replace function of SQL nbsp Server  Instead of adding a comma  use of the AS  data    will concatenate the rows with spaces  which later can be replaced with commas as the syntax written below   REPLACE           select FName AS  data     from NameList  for xml path

User · Answer

This answer will require some privilege in server to work  Assemblies are a good option for you  There are a lot of sites that explain how to create it  The one I think is very well explained is this one If you want  I have already created the assembly  and it is possible to download the DLL here  Once you have downloaded it  you will need to run the following script in your SQL Server  EXEC sp configure  show advanced options   1 RECONFIGURE  EXEC sp configure  clr strict security   1  RECONFIGURE   CREATE Assembly concat assembly     AUTHORIZATION dbo     FROM   lt PATH TO Concat dll IN SERVER gt       WITH PERMISSION SET   SAFE   GO   CREATE AGGREGATE dbo concat          Value NVARCHAR MAX        Delimiter NVARCHAR 4000      RETURNS NVARCHAR MAX   EXTERNAL Name concat assembly  Concat Concat    GO    sp configure  clr enabled   1  RECONFIGURE  Observe that the path to assembly may be accessible to server  Since you have successfully done all the steps  you can use the function like  SELECT dbo Concat field1       FROM Table1  Hope it helps     UPDATE  Since MS-SQL 2017 it is possible to use the STRING AGG  function

User · Answer

In MySQL  there is a function  GROUP CONCAT    which allows you to concatenate the values from multiple rows  Example  SELECT 1 AS a  GROUP CONCAT name ORDER BY name ASC SEPARATOR       AS people  FROM users  WHERE id IN  1 2 3   GROUP BY a

User · Answer

In Oracle  it is wm concat  I believe this function is available in the 10g release and higher

User · Answer

If you want to deal with nulls you can do it by adding a where clause or add another COALESCE around the first one   DECLARE  Names VARCHAR 8000   SELECT  Names   COALESCE COALESCE  Names               Name   Names  FROM People

User · Answer

I don t have access to a SQL Server at home  so I m guess at the syntax here  but it s more or less   DECLARE  names VARCHAR 500   SELECT  names    names         Name FROM Names

User · Answer

With TABLE type it is extremely easy  Let s imagine that your table is called Students and it has column name   declare  rowsCount INT declare  i INT   1 declare  names varchar max        DECLARE  MyTable TABLE     Id int identity    Name varchar 500    insert into  MyTable select name from Students set  rowsCount    select COUNT Id  from  MyTable   while  i  lt   rowsCount begin  set  names    names           select name from  MyTable where Id    i   set  i    i   1 end select  names   This example is tested in MS SQL Server 2008 R2

User · Answer

--SQL Server 2005   CREATE TABLE dbo Students       StudentId INT       Name VARCHAR 50        CONSTRAINT PK Students PRIMARY KEY  StudentId      CREATE TABLE dbo Subjects       SubjectId INT       Name VARCHAR 50        CONSTRAINT PK Subjects PRIMARY KEY  SubjectId      CREATE TABLE dbo Schedules       StudentId INT       SubjectId INT       CONSTRAINT PK  Schedule PRIMARY KEY  StudentId  SubjectId        CONSTRAINT FK Schedule Students FOREIGN KEY  StudentId  REFERENCES dbo Students  StudentId        CONSTRAINT FK Schedule Subjects FOREIGN KEY  SubjectId  REFERENCES dbo Subjects  SubjectId      INSERT dbo Students  StudentId  Name  VALUES      1   Mary          2   John          3   Sam          4   Alaina          5   Edward      INSERT dbo Subjects  SubjectId  Name  VALUES      1   Physics          2   Geography          3   French          4   Gymnastics      INSERT dbo Schedules  StudentId  SubjectId  VALUES      1  1       --Mary  Physics        2  1     --John  Physics        3  1     --Sam  Physics        4  2     --Alaina  Geography        5  2     --Edward  Geography    SELECT      sub SubjectId       sub Name AS  SubjectName        ISNULL  x Students      AS Students FROM     dbo Subjects sub     OUTER APPLY               SELECT              CASE ROW NUMBER   OVER  ORDER BY stu Name  WHEN 1 THEN    ELSE      END               stu Name         FROM             dbo Students stu             INNER JOIN dbo Schedules sch                 ON stu StudentId   sch StudentId         WHERE             sch SubjectId   sub SubjectId         ORDER BY             stu Name         FOR XML PATH           x  Students

User · Answer

How about this      ISNULL SUBSTRING REPLACE  select     FName as  data    from NameList for xml path                   2  300        MyList    Where the  300  could be any width taking into account the max number of items you think will show up

User · Answer

Postgres arrays are awesome  Example   Create some test data   postgres    c test You are now connected to database  test  as user  hgimenez   test   create table names  name text   CREATE TABLE                                       test   insert into names  name  values   Peter      Paul      Mary                                                              INSERT 0 3 test   select   from names   name   -------  Peter  Paul  Mary  3 rows    Aggregate them in an array   test   select array agg name  from names   array agg      -------------------    Peter Paul Mary   1 row    Convert the array to a comma delimited string   test   select array to string array agg name         from names   array to string -------------------  Peter  Paul  Mary  1 row    DONE  Since PostgreSQL 9 0 it is even easier

User · Answer

on top of Chris Shaffer answer  if your data may get repeated Such as  Tom Ali John Ali Tom Mike   Instead of having Tom Ali John Ali Tom Mike  You can use DISTINCT to avoid duplicates and get Tom Ali John Mike  DECLARE  Names VARCHAR 8000   SELECT DISTINCT  Names   COALESCE  Names              Name FROM People WHERE Name IS NOT NULL SELECT  Names

User · Answer

In SQL Server 2005  SELECT Stuff     SELECT N       Name FROM Names FOR XML PATH     TYPE     value  text   1    nvarchar max    1 2 N        In SQL Server 2016  you can use the FOR JSON syntax  i e    SELECT per ID  Emails   JSON VALUE     REPLACE        SELECT     em Email FROM Email em WHERE em Person   per ID FOR JSON PATH                            0        FROM Person per   And the result will become  Id  Emails 1   abc gmail com 2   NULL 3   def gmail com  xyz gmail com   This will work even your data contains invalid XML characters  the             is safe because if you data contain              it will be escaped to               You can replace      with any string separator    And in SQL Server 2017  Azure SQL Database  You can use the new STRING AGG function

User · Answer

Below is a simple PL SQL procedure to implement the given scenario using  basic loop  and  rownum   Table definition  CREATE TABLE  NAMES    NAME  VARCHAR2 10 BYTE        Let s insert values into this table  INSERT INTO NAMES VALUES  PETER    INSERT INTO NAMES VALUES  PAUL    INSERT INTO NAMES VALUES  MARY      Procedure starts from here  DECLARE   MAXNUM INTEGER  CNTR INTEGER    1  C NAME NAMES NAME TYPE  NSTR VARCHAR2 50    BEGIN  SELECT MAX ROWNUM  INTO MAXNUM FROM NAMES   LOOP  SELECT NAME INTO  C NAME FROM   SELECT ROWNUM RW  NAME FROM NAMES   P WHERE P RW   CNTR   NSTR    NSTR        C NAME  CNTR    CNTR   1  EXIT WHEN CNTR  gt  MAXNUM   END LOOP   dbms output put line SUBSTR NSTR 2     END    Result  PETER PAUL MARY

User · Answer

If you are on SQL Server 2017 or Azure  see Mathieu Renda answer    I had a similar issue when I was trying to join two tables with one-to-many relationships  In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily   If there is a table called STUDENTS  SubjectID       StudentName ----------      ------------- 1               Mary 1               John 1               Sam 2               Alaina 2               Edward   Result I expected was   SubjectID       StudentName ----------      ------------- 1               Mary  John  Sam 2               Alaina  Edward   I used the following T-SQL   SELECT Main SubjectID         LEFT Main Students Len Main Students -1  As  Students  FROM               SELECT DISTINCT ST2 SubjectID                                 SELECT ST1 StudentName       AS  text                    FROM dbo Students ST1                 WHERE ST1 SubjectID   ST2 SubjectID                 ORDER BY ST1 SubjectID                 FOR XML PATH                     Students          FROM dbo Students ST2        Main    You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don t need to do a sub-query   SELECT DISTINCT ST2 SubjectID       SUBSTRING                        SELECT     ST1 StudentName  AS  text                FROM dbo Students ST1             WHERE ST1 SubjectID   ST2 SubjectID             ORDER BY ST1 SubjectID             FOR XML PATH                 2  1000   Students  FROM dbo Students ST2

User · Answer

This can be useful too  create table  test  id int name varchar 10   --use separate inserts on older versions of SQL Server insert into  test values  1  Peter     1  Paul     1  Mary     2  Alex     3  Jack    DECLARE  t VARCHAR 255  SELECT  t   ISNULL  t         name  name  FROM  test WHERE id   1 select  t drop table  test   returns  Peter Paul Mary

User · Answer

With the other answers  the person reading the answer must be aware of a specific domain table such as vehicle or student  The table must be created and populated with data to test a solution   Below is an example that uses SQL Server  Information Schema Columns  table  By using this solution  no tables need to be created or data added  This example creates a comma separated list of column names for all tables in the database   SELECT     Table Name      STUFF           SELECT       Column Name         FROM INFORMATION SCHEMA Columns Columns         WHERE Tables Table Name   Columns Table Name         ORDER BY Column Name         FOR XML PATH        1  1          Columns FROM INFORMATION SCHEMA Columns Tables GROUP BY TABLE NAME

User · Answer

This worked for me  SqlServer 2016    SELECT CarNamesString   STUFF            SELECT        Name              FROM tbl cars              FOR XML PATH                 1  1        Here is the source    https   www mytecbits com   And a solution for MySql  since this page show up in Google for MySql   SELECT  Name          GROUP CONCAT DISTINCT  Name   SEPARATOR             FROM tbl cars   From MySql Documentations

User · Answer

With a recursive query you can do it   -- Create example table CREATE TABLE tmptable  NAME VARCHAR 30      -- Insert example data INSERT INTO tmptable VALUES  PETER    INSERT INTO tmptable VALUES  PAUL    INSERT INTO tmptable VALUES  MARY     -- Recurse query with tblwithrank as   select     row number   over order by name  rang   count    over   NbRow from tmptable    tmpRecursive as   select    cast name as varchar 2000   as AllName from tblwithrank  where rang 1 union all select f0     cast f0 name         f1 AllName as varchar 2000   as AllName  from tblwithrank f0 inner join tmpRecursive f1 on f0 rang f1 rang  1    select AllName from tmpRecursive where rang NbRow

User · Answer

One way you could do it in SQL nbsp Server would be to return the table content as XML  for XML raw   convert the result to a string and then replace the tags with

User · Answer

DECLARE  Names VARCHAR 8000  SELECT  name      SELECT  Names    Names         Names FROM People SELECT SUBSTRING 2   Names  7998    This puts the stray comma at the beginning   However  if you need other columns  or to CSV a child table you need to wrap this in a scalar user defined field  UDF    You can use XML path as a correlated subquery in the SELECT clause too  but I d have to wait until I go back to work because Google doesn t do work stuff at home  -

User · Answer

Here is the complete solution to achieve this   -- Table Creation CREATE TABLE Tbl   CustomerCode    VARCHAR 50    CustomerName    VARCHAR 50    Type VARCHAR 50   Items    VARCHAR 50     insert into Tbl SELECT  C0001   Thomas   BREAKFAST   Milk  union SELECT  C0001   Thomas   BREAKFAST   Bread  union SELECT  C0001   Thomas   BREAKFAST   Egg  union SELECT  C0001   Thomas   LUNCH   Rice  union SELECT  C0001   Thomas   LUNCH   Fish Curry  union SELECT  C0001   Thomas   LUNCH   Lessy  union SELECT  C0002   JOSEPH   BREAKFAST   Bread  union SELECT  C0002   JOSEPH   BREAKFAST   Jam  union SELECT  C0002   JOSEPH   BREAKFAST   Tea  union SELECT  C0002   JOSEPH   Supper   Tea  union SELECT  C0002   JOSEPH   Brunch   Roti   -- function creation GO CREATE  FUNCTION  dbo   fn GetItemsByType            CustomerCode VARCHAR 50        Type VARCHAR 50    RETURNS  ItemType TABLE    Items VARCHAR 5000    AS BEGIN          INSERT INTO  ItemType Items      SELECT  STUFF  SELECT distinct        Items           FROM Tbl           WHERE CustomerCode    CustomerCode             AND Type  Type             FOR XML PATH               1 1     as  Items        RETURN  END  GO  -- fianl Query DECLARE  cols AS NVARCHAR MAX        query  AS NVARCHAR MAX   select  cols   STUFF  SELECT distinct       QUOTENAME Type                       from Tbl             FOR XML PATH      TYPE               value       NVARCHAR MAX              1 1      set  query    SELECT CustomerCode CustomerName      cols                  from                                 select                       distinct CustomerCode                      CustomerName                      Type                      F Items                     FROM Tbl T                     CROSS APPLY  fn GetItemsByType   T CustomerCode T Type  F               x             pivot                                max Items                  for Type in       cols                    p    execute  query

User · Answer

In MySQL  there is a function  GROUP CONCAT    which allows you to concatenate the values from multiple rows  Example  SELECT 1 AS a  GROUP CONCAT name ORDER BY name ASC SEPARATOR       AS people  FROM users  WHERE id IN  1 2 3   GROUP BY a

User · Answer

A recursive CTE solution was suggested  but no code was provided   The code below is an example of a recursive CTE   Note that although the results match the question  the data doesn t quite match the given description  as I assume that you really want to be doing this on groups of rows  not all rows in the table   Changing it to match all rows in the table is left as an exercise for the reader    WITH basetable AS       SELECT         id          CAST name AS VARCHAR MAX   name           ROW NUMBER   OVER  Partition BY id ORDER BY seq  rw           COUNT    OVER  Partition BY id  recs      FROM  VALUES          1   Johnny   1            1   M   2             2   Bill   1            2   S    4            2   Preston   5            2   Esq    6            3   Ted   1            3   Theodore   2            3   Logan   3            4   Peter   1            4   Paul   2            4   Mary   3        g  id  name  seq     rCTE AS       SELECT recs  id  name  rw     FROM basetable     WHERE rw   1      UNION ALL      SELECT b recs  r ID  r name        b name name  r rw   1     FROM basetable b     INNER JOIN rCTE r ON b id   r id AND b rw   r rw   1   SELECT name FROM rCTE WHERE recs   rw AND ID 4

User · Answer

Depends on your database vendor   MySQL has concat ws  MS SQL Server expects you to do it in your client application   Update   you could also do it in an external procedure or UDF  perhaps by using a cursor or calling out to CLR code

User · Answer

In SQL Server vNext this will be built in with the STRING AGG function  read more about it here  https   msdn microsoft com en-us library mt790580 aspx

User · Answer

In MySQL  there is a function  GROUP CONCAT    which allows you to concatenate the values from multiple rows  Example  SELECT 1 AS a  GROUP CONCAT name ORDER BY name ASC SEPARATOR       AS people  FROM users  WHERE id IN  1 2 3   GROUP BY a

User · Answer

Here is the complete solution to achieve this   -- Table Creation CREATE TABLE Tbl   CustomerCode    VARCHAR 50    CustomerName    VARCHAR 50    Type VARCHAR 50   Items    VARCHAR 50     insert into Tbl SELECT  C0001   Thomas   BREAKFAST   Milk  union SELECT  C0001   Thomas   BREAKFAST   Bread  union SELECT  C0001   Thomas   BREAKFAST   Egg  union SELECT  C0001   Thomas   LUNCH   Rice  union SELECT  C0001   Thomas   LUNCH   Fish Curry  union SELECT  C0001   Thomas   LUNCH   Lessy  union SELECT  C0002   JOSEPH   BREAKFAST   Bread  union SELECT  C0002   JOSEPH   BREAKFAST   Jam  union SELECT  C0002   JOSEPH   BREAKFAST   Tea  union SELECT  C0002   JOSEPH   Supper   Tea  union SELECT  C0002   JOSEPH   Brunch   Roti   -- function creation GO CREATE  FUNCTION  dbo   fn GetItemsByType            CustomerCode VARCHAR 50        Type VARCHAR 50    RETURNS  ItemType TABLE    Items VARCHAR 5000    AS BEGIN          INSERT INTO  ItemType Items      SELECT  STUFF  SELECT distinct        Items           FROM Tbl           WHERE CustomerCode    CustomerCode             AND Type  Type             FOR XML PATH               1 1     as  Items        RETURN  END  GO  -- fianl Query DECLARE  cols AS NVARCHAR MAX        query  AS NVARCHAR MAX   select  cols   STUFF  SELECT distinct       QUOTENAME Type                       from Tbl             FOR XML PATH      TYPE               value       NVARCHAR MAX              1 1      set  query    SELECT CustomerCode CustomerName      cols                  from                                 select                       distinct CustomerCode                      CustomerName                      Type                      F Items                     FROM Tbl T                     CROSS APPLY  fn GetItemsByType   T CustomerCode T Type  F               x             pivot                                max Items                  for Type in       cols                    p    execute  query

User · Answer

This answer will require some privilege in server to work  Assemblies are a good option for you  There are a lot of sites that explain how to create it  The one I think is very well explained is this one If you want  I have already created the assembly  and it is possible to download the DLL here  Once you have downloaded it  you will need to run the following script in your SQL Server  EXEC sp configure  show advanced options   1 RECONFIGURE  EXEC sp configure  clr strict security   1  RECONFIGURE   CREATE Assembly concat assembly     AUTHORIZATION dbo     FROM   lt PATH TO Concat dll IN SERVER gt       WITH PERMISSION SET   SAFE   GO   CREATE AGGREGATE dbo concat          Value NVARCHAR MAX        Delimiter NVARCHAR 4000      RETURNS NVARCHAR MAX   EXTERNAL Name concat assembly  Concat Concat    GO    sp configure  clr enabled   1  RECONFIGURE  Observe that the path to assembly may be accessible to server  Since you have successfully done all the steps  you can use the function like  SELECT dbo Concat field1       FROM Table1  Hope it helps     UPDATE  Since MS-SQL 2017 it is possible to use the STRING AGG  function

User · Answer

SELECT PageContent   Stuff          SELECT PageContent         FROM dbo InfoGuide         WHERE CategoryId    CategoryId           AND SubCategoryId    SubCategoryId         for xml path      type       value    1    nvarchar max         1  1      FROM dbo InfoGuide info

User · Answer

This can be useful too  create table  test  id int name varchar 10   --use separate inserts on older versions of SQL Server insert into  test values  1  Peter     1  Paul     1  Mary     2  Alex     3  Jack    DECLARE  t VARCHAR 255  SELECT  t   ISNULL  t         name  name  FROM  test WHERE id   1 select  t drop table  test   returns  Peter Paul Mary

User · Answer

Oracle 11g Release 2 supports the LISTAGG function   Documentation here   COLUMN employees FORMAT A50  SELECT deptno  LISTAGG ename       WITHIN GROUP  ORDER BY ename  AS employees FROM   emp GROUP BY deptno       DEPTNO EMPLOYEES ---------- --------------------------------------------------         10 CLARK KING MILLER         20 ADAMS FORD JONES SCOTT SMITH         30 ALLEN BLAKE JAMES MARTIN TURNER WARD  3 rows selected    Warning  Be careful implementing this function if there is possibility of the resulting string going over 4000 characters   It will throw an exception   If that s the case then you need to either handle the exception or roll your own function that prevents the joined string from going over 4000 characters

User · Answer

A ready-to-use solution  with no extra commas   select substring           select      Name AS  data    from Names for xml path              3  255  as  MyList    An empty list will result in NULL value  Usually you will insert the list into a table column or program variable  adjust the 255 max length to your need    Diwakar and Jens Frandsen provided good answers  but need improvement

[sql] How to concatenate text from multiple rows into a single text string in SQL server?

Examples related to sql

Examples related to sql-server

Examples related to csv

Examples related to string-concatenation

Examples related to group-concat