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


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


Related Articles

How to get rows into comma separated values column in sql

How to get rows into comma separated values column in sql. Fetching rows values into comma separated column value is required lots of time in our projects. So I tried to achieve this and lastly found one solution. Use this to get all row values into one single column value with comma separation.

Split Comma separated values into Columns

In this article, I explain about how to split a string with delimiter into list of rows. De limiter can be comma or plus our requirement is to convert a single row of a table into multiple rows based on number of delimiter.

More articles: Comma separated value

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: nehal rana14 Jul 2009 Member Level: Bronze   Points : 2

declare @tmp table(id int identity(1,1),pk int,name nvarchar(255))

insert into @tmp values(1, 'aa')
insert into @tmp values(1, 'ab')
insert into @tmp values(1, 'ac')
insert into @tmp values(2, 'bb')
insert into @tmp values(2, 'cc')
insert into @tmp values(3, 'ca')
insert into @tmp values(3, 'cb')
insert into @tmp values(2, 'bd')
insert into @tmp values(1, 'cc')


declare @rowcount int
declare @res nvarchar(max)

select @rowcount=count(*) from @tmp

declare @i int
set @i=1

declare @pk int
declare @name nvarchar(255)
create table #res(data int,val nvarchar(255))
declare @match int
while @i<=@rowcount
begin
select @pk=pk,@name=[name] from @tmp where id=@i
select @match=count(*) from #res where data=@pk
if @match<=0
begin
insert into #res(data,val) values(@pk,@name)
end
else
begin
update #res set val=val+','+@name where data=@pk
end
set @i = @i + 1
end
select * from #res
drop table #res

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



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: