Concatenate Different Rows For Two Columns (T-SQL



  
CREATE TABLE #SantasHelpers (work_dt datetime, fname varchar(20), lname varchar(25), title tinyint)

INSERT INTO #SantasHelpers VALUES ('4/1/2013', 'Tiny', 'Tim', 0)
INSERT INTO #SantasHelpers VALUES ('4/1/2013', 'Mad', 'Rapper', 0)
INSERT INTO #SantasHelpers VALUES ('4/2/2013', 'Little', 'Joe', 0)
INSERT INTO #SantasHelpers VALUES ('4/2/2013', 'Pretty', 'Missy', 0)
INSERT INTO #SantasHelpers VALUES ('4/3/2013', 'Rough', 'Ralph', 1)
INSERT INTO #SantasHelpers VALUES ('4/3/2013', 'Big', 'Barry', 0)
INSERT INTO #SantasHelpers VALUES ('4/4/2013', 'Fancy', 'Dancy', 0)
INSERT INTO #SantasHelpers VALUES ('4/5/2013', 'Wild', 'Willie', 0)
INSERT INTO #SantasHelpers VALUES ('4/5/2013', 'Silly', 'Sonia', 1)
INSERT INTO #SantasHelpers VALUES ('4/8/2013', 'Tiny', 'Tim', 0)
INSERT INTO #SantasHelpers VALUES ('4/8/2013', 'Mad', 'Rapper', 0)
INSERT INTO #SantasHelpers VALUES ('4/9/2013', 'Little', 'Joe', 0)
INSERT INTO #SantasHelpers VALUES ('4/9/2013', 'Pretty', 'Missy', 0)
INSERT INTO #SantasHelpers VALUES ('4/10/2013', 'Rough', 'Ralph', 1)
INSERT INTO #SantasHelpers VALUES ('4/10/2013', 'Big', 'Barry', 0)
INSERT INTO #SantasHelpers VALUES ('4/11/2013', 'Fancy', 'Dancy', 0)
INSERT INTO #SantasHelpers VALUES ('4/12/2013', 'Wild', 'Willie', 0)
INSERT INTO #SantasHelpers VALUES ('4/12/2013', 'Silly', 'Sonia', 1)

;with mycte as (
Select  work_dt
, col1 = Case When title = 0 Then fname + ' ' + lname End
, col2 = Case When title = 1 Then fname + ' ' + lname End
From #SantasHelpers
Group By work_dt, fname, lname, title)
 


SELECT   work_dt, stuff((select ',' + m1.col1
from mycte m1 WHERE m.work_dt=m1.work_dt
 for XML PATH('')),1,1,'') as [Santas Helpers]
 ,stuff((select ',' + m1.col2
from mycte m1 WHERE m.work_dt=m1.work_dt
 for XML PATH('')),1,1,'')  as [Elves]
from mycte m 
Group By work_dt



drop table #SantasHelpers

--http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1c135570-49e2-42fd-982f-c208acf6ac9f/grouping-values-from-multiples-rows-into-single-column

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s