COALESCE STATEMENT TO CONCATENATE ALL THE ROWS OF A PARTICULAR COLUMN IN SQL SERVER 2005
–Create a table
CREATE TABLE EMPLOYEE
(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EMPNAME VARCHAR(100),
SALARY DECIMAL
)
–Insert some data in the above table
SELECT * FROM EMPLOYEE
OUTPUT
__________________
1 Pawan 180000
2 Gaurav 80000
3 Saurabh 80000
4 Pawan 830000
5 Neelam 200000
–Try out the below Query
DECLARE @EMPNAME VARCHAR(1024)
SELECT @EMPNAME = COALESCE(@EMPNAME + ‘ , ‘, ”) + LTRIM(RTRIM(EMPNAME)) FROM EMPLOYEE
PRINT @EMPNAME
OUTPUT
_____________________
Pawan , Gaurav , Saurabh , Pawan , Neelam.
ONE MORE ALTERNATIVE IS THERE
DECLARE @EMPNAME VARCHAR(1024)
SELECT @EMPNAME = ISNULL(@EMPNAME + ‘ , ‘, ”) + LTRIM(RTRIM(EMPNAME)) FROM EMPLOYEE
PRINT @EMPNAME
OUTPUT
_____________________
Pawan , Gaurav , Saurabh , Pawan , Neelam.
NOTE : COALESCE will return the thing in varchar2 that means is the string is too large then it will not work.
Pawan Kumar
Pawankkmr@hotmail.com