Using coalesce to build comma separated values as results from SQL Server

Posted Date: 24 Dec 2007 |Updated: 24-Dec-2007 |Category: SQL |Author: Vadivel Mohanakrishnan |Member Level: Gold |Points: 7 |

This article explain about how to Using coalesce to build comma separated values as results from SQL Server

This code will introduce you to a simple solution using 'Coalesce' in SQL Server for building comma separated strings.


--Sample table schema
Create table SchemaID
(
[ID] smallint,
SchemaID int NOT NULL
)
go

--Dummy insert statements
Insert into SchemaID values (1,12)
Insert into SchemaID values (1,13)
Insert into SchemaID values (1,14)

Insert into SchemaID values (2,15)
Insert into SchemaID values (2,16)
Insert into SchemaID values (2,17)
Insert into SchemaID values (2,18)

--Solution
Declare @ID varchar(100) 
Select @id=Coalesce(@ID + ', ', '') + Cast(SchemaID AS varchar(5)) From SchemaID Where [ID] = 1 
SELECT @ID

FYI, I have provided this as an solution to a question here - http://www.dotnetspider.com/qa/Question116526.aspx

Comments

Author: Raja Jegan R10 Jun 2009 Member Level: Bronze Points : 1

Vadivel,
Can you kindly share scripts if you have any to create output like this

ID SchemaID
1 12,13,14
2 15,16,17,18

Author: Raja Jegan R15 Jul 2009 Member Level: Bronze Points : 2

Thanks Nehal..
Solved it on my own after I raised that question.

CREATE TABLE #temp_result ( a varchar(20), b varchar(1000));

Declare @id_con varchar(100);
DECLARE @id_in varchar(100);
DECLARE loop_val CURSOR FOR
SELECT DISTINCT cast(id AS varchar(20))
FROM SchemaID;

OPEN loop_val;

FETCH NEXT FROM loop_val
INTO @id_in;

WHILE @@FETCH_STATUS = 0
BEGIN
Select @id_con=Coalesce(@id_con + ', ', '') + Cast(SchemaID AS varchar(5)) From SchemaID Where [ID] = @id_in
INSERT INTO #temp_result VALUES (@id_in, @id_con);
SET @id_con = NULL;
FETCH NEXT FROM loop_val
INTO @id_in;
END

CLOSE loop_val;
DEALLOCATE loop_val;

SELECT * FROM #temp_result
DROP TABLE #temp_result

Author: Abhay Kumar Raut14 Aug 2009 Member Level: Gold Points : 1

Hi Vadivel Mohanakrishnan,

good one .

keep it up.

contribute more.

Thanks and Regards
Abhay

Author: Pandian S14 Aug 2009 Member Level: Gold Points : 2

Dear Raja Jegan R / nehal rana

Can u go thru the simple/tuned way and give your feedback.

;WITH Concatenation(ID,SchemaID) AS
(SELECT A.ID,
(SELECT CAST(B.SchemaID AS VARCHAR) + ',' FROM SchemaID B WHERE B.ID=A.ID FOR XML PATH('')) 'SchemaID'
FROM SchemaID A
GROUP BY A.ID
)
SELECT ID,LEFT(SchemaID,LEN(SchemaID)-1) 'SchemaID' FROM Concatenation
GO

Output :
-------------

ID SchemaID
------ -----------
1 12,13,14
2 15,16,17,18

Cheers

Author: Raja Jegan R18 Aug 2009 Member Level: Bronze Points : 1

Pandian S,
Your approach looks optimal..
Thanks for sharing.

Regards,
RRJ

Return to Article Index

Digital Marketing by SpiderWorks Technologies, Kochi - India. ©