Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the best way to collapse the rows of a SELECT into a string?

Tags:

sql

sql-server

In a SQL statement ( or procedure ) I want to collapse the rows of this table into a single comma delimited string.

simpleTable

id  value
--  ----- 
1    "a"
2    "b"
3    "c"

Collapse to:

"a, b, c"
like image 536
Justin Tanner Avatar asked May 04 '09 23:05

Justin Tanner


3 Answers

You can concatenate using an embedded 'set' statement in a query:

declare @combined varchar(2000)
select @combined = isnull(@combined + ', ','') + isnull(value,'')
from simpleTable

print @combined

(Note that the first isnull() initialises the string, and the second isnull() is especially important if there's any chance of nulls in the 'value' column, because otherwise a single null could wipe out the whole concatenation)

(edited code and explanation after comments)

Edit (ten years later):

SQL Server 2017 introduced the STRING_AGG() function which provides an official way of concatenating strings from different rows. Like other aggregation functions such as COUNT(), it can be used with GROUP BY.

So for the example above you could do:

select string_agg(value, ', ')
from simpleTable

If you had some other column and you wanted to concatenate for values of that column, you would add a 'group by' clause, e.g:

select someCategory, string_agg(value, ', ') as concatValues
from simpleTable
group by someCategory

Note string_agg will only work with SQL 2017 and above.

like image 75
codeulike Avatar answered Oct 30 '22 20:10

codeulike


This will only work in MSSQL 2005+

select value + ',' from simpletable for xml path ('')

..one way to prevent the extra comma:

select case(row_number() over (order by id))
when 1 then value else ',' + value end
from simpletable
for xml path ('')
like image 35
gordy Avatar answered Oct 30 '22 18:10

gordy


DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

Results:

1, 2, 4
like image 44
Daniel Avatar answered Oct 30 '22 18:10

Daniel