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

Advertisements