Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CONCENTATION OF ROWS BY USING COALESCE Expand / Collapse
Author
Message
Posted Wednesday, October 14, 2009 3:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse