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
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
