Comma Separated Column Value thru T-SQL

subhendude · December 8th, 2006, 01:04 AM

I have 1 table containing
table 1
=======
c1 c2 c3
================
1 A 11
1 A 12
2 B 14
2 B 15
2 B 17
3 C 10
================

From tha above table, I want to return a resultset which seems like below

c1 c2 c3
=====================
1 A 11,12
2 B 14,15,17
3 C 10
=====================

Kindly give me the SQL Query for the above problem..

Jeff Moden · December 8th, 2006, 08:56 AM

Untested...

Create a function similar to this...

Code:

 CREATE FUNCTION dbo.MakeCsvC3 (@pC2 AS VARCHAR(10))
RETURNS VARCHAR(8000) 
AS
BEGIN
        DECLARE @oResult VARCHAR(8000)
         SELECT @oResult= COALESCE(@oResult+',','')+CAST(C3 AS VARCHAR(10)
           FROM yourtable WITH (NOLOCK)
          WHERE C2 = @pC2
          ORDER BY C3
         RETURN @oResult
END

... call it like this...

Code:

 SELECT DISTINCT
        C1,C2,dbo.MakeCsvC3(C2)
   FROM yourtable

--Jeff Moden

sshelper · December 12th, 2006, 01:42 PM

Here's a link that discusses the same answer that Jeff has provided:

http://www.sql-server-helper.com/tip...ed-output.aspx

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp