Mahmoud ElMansy: knowledge meant to be free

TSQL – Concatenate Rows Into On Column Using For XML Path()

This case always happen when you are working  with large business that you need Concatenate rows into one string .

one of the methods to do that is to Use XML Path() to turn rows into one XML with no tags.

As follows
1)  Declare To Tables With one to many relation

DECLARE @Categories TABLE
(
CategoryID INT,
Category_Code VARCHAR(16),
Notes VARCHAR(128)
);

DECLARE @Products TABLE
(
ProductID INT,
CategoryID INT,
Product_Code VARCHAR(16),
Notes VARCHAR(128)
);

2) Fill Tables with Dummy Data

INSERT @Categories SELECT 1,'Cat_1','Notes Notes Notes'
UNION ALL SELECT 2,'Cat_2','Notes Notes Notes'
UNION ALL SELECT 3,'Cat_3','Notes Notes Notes';

INSERT @Products SELECT 1,1,'Product_1','Notes Notes Notes'
UNION ALL SELECT 1,1,'Product_1','Notes Notes Notes'
UNION ALL SELECT 2,1,'Product_2','Notes Notes Notes'
UNION ALL SELECT 3,1,'Product_3','Notes Notes Notes'
UNION ALL SELECT 4,2,'Product_4','Notes Notes Notes'
UNION ALL SELECT 5,2,'Product_5','Notes Notes Notes'
UNION ALL SELECT 6,2,'Product_6','Notes Notes Notes'
UNION ALL SELECT 7,3,'Product_7','Notes Notes Notes';

3) Run The Query

SELECT *,
STUFF( (SELECT ', ' + p.Product_Code FROM @Products AS p WHERE p.CategoryID=cat.CategoryID FOR XML PATH('')),1,1,'') Products
FROM @Categories AS cat

The result
8-21-2014 11-05-14 AM

8-21-2014 11-05-27 AM

8-21-2014 11-06-01 AM