CONCENTATION OF ROWS BY USING COALESCE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Carl P. Anderson » CONCENTATION OF ROWS BY USING COALESCE

CONCENTATION OF ROWS BY USING COALESCE

Rate Topic

Display Mode

Topic Options

Author

Message

tmahaboobbasha

Posted Wednesday, October 14, 2009 3:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 04, 2012 4:11 AM
Points: 6, Visits: 25

Hi

We Can concenate Rows by using the COALESCE Statement as follows.
For example if we want to concenate the Name data as "Ram,Shyam,Basha,Shiva" from Emp table.

CREATE TABLE #Emp(ID INT, Name VARCHAR(40))

INSERT #Emp SELECT 1, 'Ram'
INSERT #Emp SELECT 2, 'Shyam'
INSERT #Emp SELECT 3, 'Basha'
INSERT #Emp SELECT 4, 'Shiva'

ID Name
1 Ram
2 Shyam
3 Basha
4 Shiva

DECLARE @EmplList VARCHAR(400)

SELECT @EmpList = COALESCE(@EmpList,'')+
CASE WHEN COALESCE(@EmpList,'') <> '') AND COALESCE(Name,'') <> '' THEN ',' ELSE '' END +
COALESCE(Name,'') FROM #Emp Order BY ID

SELECT @EmpList

DROP TABLE #Emp

Result
Ram,Shyam,Basha,Shiva

Post #802594

« Prev Topic | Next Topic »

Permissions