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

How to aggregate BIT Columns with Group By in SQL Server

This is a very common requirement in sql when you are deveploping applications.
You can achive this with very simple query, using cast and min sql functions.

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


-- This simple sql query will return user wise aggregated report_available column value
select username as UserName, cast(min(cast(report_available as int)) as bit) as 'Is Report Available'
from user_reports group by username

SSIS Error : Failed to save package file, Element not found.

There could be many reasons that can cause this error but in my case the reason was that MSXML6.dll was not registered on my machine. So I just registered this dll to resolve this error.

To register MSXML6.dll on your machine, follow these steps:
1. From the Start menu, click Run.
2. Enter the following command: regsvr32 %windir%\system32\msxml6.dll.
3. Click OK.

Note - Replace %windir% with the path of windows folder on your machine. I my case it was - c:\windows