SQL Server 2005 - Showing ResultSet in a Single Row OR in a comma separated string format.

Scenario:- Suppose You have a table abc (Sal_Id int ,Emp_Id int).
The Following Query is fired on the table

select Emp_Id
FROM abc
WHERE Sal_Id=1

Query gives result with following rows

1
2
4

Your requirement is to get these values in a single row. This can be achieved by making use of COALESCE in SQL Server.
The above Query can be modified as follows to fetch the rows in required format.

DECLARE @EmpList varchar(100)

SELECT @EmpList = COALESCE(@EmpList,'')+
CAST(Emp_Id AS VARCHAR(3))
FROM abc
WHERE Sal_Id=1

select @EmpList

This will fetch result as

124

If you want result as a Comma Separated string, change COALESCE function as COALESCE(@EmpList,',' ''). You will get result as

1,2,4

Popular posts from this blog

Asp.Net Save File on client machine

Recently I had a task to save file on client machine. We usually save file on server and in ASP.NET the more familiar task to save on client side was to export gridview results to excel file. This time the task was to save an image on client machine. Following is the code that I followed.   <pre name="code" class="html"> byte[] fileBytes = File.ReadAllBytes("D:\\Projects\\TestClientDownload\\images\\banner_ad_1.jpg");        Response.ClearContent();        Response.ClearHeaders();        Response.ContentType = "text/plain";        Response.AppendHeader("content-disposition", "attachment; filename=" + "banner_ad_1.jpg");        Response.AppendHeader("content-length", fileBytes.Length.ToString());       ...
Read more

Get Unused Stored Procedures from Sql Server 2008

While working in projects, I always used to wonder about maintaining Stored Procedures in Sql Server. The way in which we organize them is that we go on adding the required stored procedures with some name convention so that we can identify what is the module to which this SP refers. But as the project goes ahead modifications come, we go on adding required Stored Procedures and some Stored Procedures become obsolete because of business rule changes. But as far as I have observed in IT projects, these stored procedures are kept as it is and when the project completes no one has any idea about which Stored Procedures are currently in use and which are obsolete. But I have come across this post , which shows how to get probably unused SPs from Sql Server 2008. The query is as follows, which returns SPs which are not in procedure cache. //The first part gives list of all SPs and also works in SQL2005 Select p.name From sys.procedures as p wh...
Read more

Cross Apply Incorrect syntax near '.'

Recently I came across an issue in Sql Server 2005, while running CROSS APPLY. The error says that Incorrect syntax near '.'. I tried to figure out all syntax that I could apply but it did not resolve the issue. Actually we need to set sp_dbcmptlevel level to 90. There are good blog posts for this here and here , which can save your valuable time.
Read more