Convert multiple rows into one with comma as separator

Question

If I issue SELECT username FROM Users I get this result    username -------- Paul John Mary   but what I really need is one row with all the values separated by comma  like this    Paul  John  Mary   How do I do this

User · Answer

If you re executing this through PHP  what about this    hQuery   mysql query  SELECT   FROM users    while  hRow   mysql fetch array  hQuery          hOut     hRow  username              hOut   substr  hOut  0  strlen  hOut  - 1   echo  hOut

User · Answer

you can use stuff   to convert rows as comma separated values  select EmployeeID  stuff     SELECT       FPProjectMaster GroupName        FROM     FPProjectInfo AS t INNER JOIN               FPProjectMaster ON t ProjectID   FPProjectMaster ProjectID       WHERE   t EmployeeID   FPProjectInfo EmployeeID                And t STatusID   1               ORDER BY t ProjectID        for xml path              1 1     as name csv from FPProjectInfo group by EmployeeID    Thanks  AlexKuznetsov for the reference to get this answer

User · Answer

good review of several approaches   http   blogs msmvps com robfarley 2007 04 07 coalesce-is-not-the-answer-to-string-concatentation-in-t-sql   Article copy -   Coalesce is not the answer to string concatentation in T-SQL I ve seen many posts over the years about using the COALESCE function to get string concatenation working in T-SQL  This is one of the examples here  borrowed from Readifarian Marc Ridey    DECLARE  categories varchar 200  SET  categories   NULL  SELECT  categories   COALESCE  categories             Name FROM Production ProductCategory  SELECT  categories   This query can be quite effective  but care needs to be taken  and the use of COALESCE should be properly understood  COALESCE is the version of ISNULL which can take more than two parameters  It returns the first thing in the list of parameters which is not null  So really it has nothing to do with concatenation  and the following piece of code is exactly the same - without using COALESCE   DECLARE  categories varchar 200  SET  categories       SELECT  categories    categories         Name FROM Production ProductCategory  SELECT  categories   But the unordered nature of databases makes this unreliable  The whole reason why T-SQL doesn t  yet  have a concatenate function is that this is an aggregate for which the order of elements is important  Using this variable-assignment method of string concatenation  you may actually find that the answer that gets returned doesn t have all the values in it  particularly if you want the substrings put in a particular order  Consider the following  which on my machine only returns   Accessories   when I wanted it to return   Bikes Clothing Components Accessories    DECLARE  categories varchar 200  SET  categories   NULL  SELECT  categories   COALESCE  categories             Name FROM Production ProductCategory ORDER BY LEN Name   SELECT  categories   Far better is to use a method which does take order into consideration  and which has been included in SQL2005 specifically for the purpose of string concatenation - FOR XML PATH      SELECT       Name FROM Production ProductCategory ORDER BY LEN Name  FOR XML PATH        In the post I made recently comparing GROUP BY and DISTINCT when using subqueries  I demonstrated the use of FOR XML PATH      Have a look at this and you ll see how it works in a subquery  The  STUFF  function is only there to remove the leading comma   USE tempdb  GO CREATE TABLE t1  id INT  NAME VARCHAR MAX    INSERT t1 values  1  Jamie    INSERT t1 values  1  Joe    INSERT t1 values  1  John    INSERT t1 values  2  Sai    INSERT t1 values  2  Sam    GO  select     id      stuff           select       t  name          from t1 t         where t id   t1 id         order by t  name          for xml path           1 1     as name csv from t1 group by id      FOR XML PATH is one of the only situations in which you can use ORDER BY in a subquery  The other is TOP  And when you use an unnamed column and FOR XML PATH      you will get a straight concatenation  with no XML tags  This does mean that the strings will be HTML Encoded  so if you re concatenating strings which may have the  lt  character  etc   then you should maybe fix that up afterwards  but either way  this is still the best way of concatenating strings in SQL Server 2005

User · Answer

DECLARE  EmployeeList varchar 100   SELECT  EmployeeList   COALESCE  EmployeeList                   CAST Emp UniqueID AS varchar 5   FROM SalesCallsEmployees WHERE SalCal UniqueID   1  SELECT  EmployeeList   source  http   www sqlteam com article using-coalesce-to-build-comma-delimited-string

User · Answer

This should work for you   Tested all the way back to SQL 2000   create table  user  username varchar 25    insert into  user  username  values   Paul   insert into  user  username  values   John   insert into  user  username  values   Mary    declare  tmp varchar 250  SET  tmp      select  tmp    tmp   username        from  user  select SUBSTRING  tmp  0  LEN  tmp

User · Answer

In SQLite this is simpler  I think there are similar implementations for MySQL  MSSql and Orable  CREATE TABLE Beatles  id integer  name string    INSERT INTO Beatles VALUES  1   Paul    INSERT INTO Beatles VALUES  2   John    INSERT INTO Beatles VALUES  3   Ringo    INSERT INTO Beatles VALUES  4   George    SELECT GROUP CONCAT name       FROM Beatles

User · Answer

A clean and flexible solution in MS SQL Server 2005 2008 is to create a CLR Agregate function   You ll find quite a few articles  with code  on google   It looks like this article walks you through the whole process using C

User · Answer

building on mwigdahls answer   if you also need to do grouping here is how to get it to look like   group  csv  group1    paul  john   group2    mary       --drop table  user create table  user  groupName varchar 25   username varchar 25    insert into  user  groupname  username  values   apostles    Paul   insert into  user  groupname  username  values   apostles    John   insert into  user  groupname  username  values   family   Mary     select     g1 groupname       stuff           select        g username         from  user g                 where g groupName   g1 groupname                 order by g username         for xml path           1 2     as name csv from  user g1 group by g1 groupname

User · Answer

You can use this query to do the above task   DECLARE  test NVARCHAR max    SELECT  test   COALESCE  test              field2 FROM  test SELECT field2    test    For detail and step by step explanation visit the following link http   oops-solution blogspot com 2011 11 sql-server-convert-table-column-data html

User · Answer

select    distinct       stuff           select       u username         from users u         where u username   username         order by u username         for xml path           1 1     as userlist from users group by username   had a typo before  the above works

[tsql] Convert multiple rows into one with comma as separator

Examples related to tsql

Examples related to rows

Examples related to csv