How to concantenate multiple rows into Single Row

Hi,

In many cases we face the issue making multiple rows into single rows. Please go through below script to make multiple rows into single rows.

CREATE TABLE #TEMP
(
EMPID INT,
EMPNAME VARCHAR(100)
)

INSERT INTO #TEMP
SELECT 101,'EMP1'
UNION ALL
SELECT 102,'EMP2'
UNION ALL
SELECT 103,'EMP3'
--- WHEN YOU QUERY THE TABLE YOU WILL GET BELOW OUT PUT---
SELECT * FROM #TEMP

EMPID EMPNAME
101 EMP1
102 EMP2
103 EMP3


DECLARE @EMPNAME VARCHAR(100)
SELECT @EMPNAME =COALESCE(@EMPNAME+',','')+EMPNAME
FROM #TEMP

PRINT @EMPNAME

RESULT:

EMP1,EMP2,EMP3