Saturday, December 11, 2010

How to get Comma Separated Values (CSV) from Table Column

This is another very common requirement while developing sql code for the applications.
And again this can be done in various ways...one of them is here...

Example:

create table user_reports
(id int identity, username varchar(20),report_name varchar(20), report_available bit)
go
insert into user_reports(username,report_name,report_available)
select 'Ajay', 'report1',1
union all
select 'Ajay', 'report2',1
union all
select 'Ajay', 'report3',1
union all
select 'Ajay', 'report4',1
union all
select 'John', 'report5',0
union all
select 'John', 'report6',1
union all
select 'John', 'report2',0
union all
select 'Tony', 'report1',1
union all
select 'Tony', 'report7',1
union all
select 'Ram', 'report3',1
union all
select 'Ram', 'report4',0
union all
select 'Ram', 'report8',1


-- CSV report list for all users
select stuff(
(select ',' + ur.report_name
from user_reports ur
order by ur.report_name
for xml path('')),1,1,'') as 'Report List'
go

-- CSV report list for a given user
select stuff(
(select ',' + ur.report_name
from user_reports ur where username = 'Ajay'
order by ur.report_name
for xml path('')),1,1,'') as 'Report List'
go

-- CSV report list user wise
select ur1.username,stuff(
(select ',' + ur.report_name
from user_reports ur where ur.username=ur1.username
order by ur.report_name
for xml path('')),1,1,'') as 'Report List'
from user_reports ur1 group by ur1.username
go

No comments: