Pages

Thursday, May 28, 2009

Undocumented feature to concatenate strings from a column into a single row

There are several ways to create one column with comma separated values from multiple rows. Today, I would like to show you, how to achieve this with very simple query using XML features of SQL Server.
set nocount on
-- Decalare Table Variable
Declare @t Table (c1 int)
-- Insert data into Table
Insert @t
Select 10 Union All
Select 20 Union All
Select 30 Union All
Select 40 Union All
Select 50 Union All
Select 60
-- Select data from table (in Rows)
SELECT C1 AS input FROM @t
-- Select data from table using XML(As One single column)
SELECT (
SELECT CAST(C1 AS VARCHAR(20)) + ','
FROM @t
FOR XML PATH ('')
) as Comma_Separated_String
set nocount off


-- Query Output

input
-----------
10
20
30
40
50
60


Comma_Separated_String
-------------------------------

10,20,30,40,50,60,

No comments: