Merge Row into Column With T-SQL




Create TABLE #tbl1 (id int, col1 nvarchar(50))
Create TABLE #tbl2 (id int, col2 nvarchar(50))
GO


INSERT INTO #tbl1 Values( 1 ,'A'),(2, 'B') 
GO

INSERT INTO #tbl2 VALUES (1 ,'C'),(1, 'E'),(2, 'D'),(2, 'F'),(2, 'G')
GO


SELECT * FROM #tbl1
SELECT * FROM #tbl2

 


Select id, col1, CombinedList = STUFF(( Select '; ' + convert(varchar,c.col2) as [text()]
From (SELECT a.id,a.col2, b.col1 FROM #tbl2 a LEFT JOIN #tbl1 b on a.id=b.id) c
Where c.id = b.id AND c.col1 = b.col1
Order by id
For XML Path ('')), 1, 1, '')

From (SELECT a.id,a.col2, b.col1 FROM #tbl2 a LEFT JOIN #tbl1 b on a.id=b.id) b
Group by id, col1
Order by id;

 

DROP TABLE #tbl1
DROP TABLE #tbl2



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