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,
Thursday, May 28, 2009
Undocumented feature to concatenate strings from a column into a single row
Labels:
DotNetVJ,
Vijaya Kadiyala
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment