Convert multiple lines to one semicolon as a separator

Question

If I issue SELECT username FROM Users , I get this result:

 username
 --------
 Paul
 John
 Mary

but I really need one line with all values ​​separated by a comma, for example:

 Paul, John, Mary

How to do it?

Answer 1

This should work for you. Tested up 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)) 

Answer 2

  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 work described above

Answer 3

good overview of several approaches:

http://blogs.msmvps.com/robfarley/2007/04/07/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql/

Copy of article -

Coalesce is not a response to string concatenation in T-SQL. I have seen many reports over the years about using the COALESCE function to get string concatenation working in T-SQL. This is one example here (borrowed from Readifarian Marc Ridey).

 DECLARE @categories varchar(200) SET @categories = NULL SELECT @categories = COALESCE(@categories + ',','') + Name FROM Production.ProductCategory SELECT @categories 

This request can be very effective, but care must be taken to ensure that it is correctly understood, and the use of COALESCE. COALESCE is an ISNULL version that can take more than two parameters. It returns the first in the parameter list, which is not null. So this really has nothing to do with concatenation, and the following code snippet is exactly the same - without using COALESCE:

 DECLARE @categories varchar(200) SET @categories = '' SELECT @categories = @categories + ',' + Name FROM Production.ProductCategory SELECT @categories 

But the disordered nature of the databases makes this unreliable. The whole reason T-SQL does not yet have a concatenate function is because it is a collection for which a sequence of elements is important. Using this account assignment method for account assignment, you can actually find that the answer that is returned does not have all the values ​​in it, especially if you want the substrings to be placed in a specific order. Consider the following, which on my machine returns "Accessories" when I wanted it to come back "," Bicycles, clothes, components, accessories ":

 DECLARE @categories varchar(200) SET @categories = NULL SELECT @categories = COALESCE(@categories + ',','') + Name FROM Production.ProductCategory ORDER BY LEN(Name) SELECT @categories 

It is much better to use a method that takes order into account and which was included in SQL2005 specifically for string concatenation - FOR XML PATH ('')

 SELECT ',' + Name FROM Production.ProductCategory ORDER BY LEN(Name) FOR XML PATH('') 

In a post I recently compared with GROUP BY and DISTINCT when using subqueries, I demonstrated the use of FOR XML PATH (''). Look at this and you will see how it works in the subquery. The "STUFF" function is available only 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 direct concatenation without XML tags. This means that the strings will be encoded in HTML, so if you concatenate strings that may have <character (etc.), then you may later correct this, but in any case, this is the best way to concatenate strings in SQL Server 2005

Answer 4

 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

Answer 5

You can use this query to accomplish the above task:

 DECLARE @test NVARCHAR(max) SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2 = @test 

For a detailed and step-by-step explanation, visit the following link http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html

Answer 6

building on mwigdahls answers. if you also need to do grouping, here is how to do it:

 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 

Answer 7

A clean and flexible solution in MS SQL Server 2005/2008 is to create the Agregate CLR function.

You will find many articles (with code) on google .

It looks like this article will guide you through the whole process using C #.

Answer 8

In SQLite, this is easier. 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; 

Answer 9

you can use stuff () to convert strings 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 link to get this answer.

Answer 10

If you are doing 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;