Merge values from multiple rows into one row

Today I needed to make a report to show a few Item in a single column attribute. What I needed is to join values from many rows into a single column attribute.

A nice example that I found quickly from a blog SQL Server Curry saved me quite some time so I decided to post this on my blog as well.

Example:

Items in table ItemMaster with :
ItemMaster  -> 

Shirt
Trouser
TShirt
Capri
Jeans
Barmuda

Result I wanted to get is:

[Shirt, Trouser, TShirt, Capri, Jeans, Barmuda] in a single attribute column


My prefered way that I found on blog was to use MS SQL STUFF:

SELECT DISTINCT STUFF( (SELECT ', ' + [Item] from [ItemMaster] FOR XMLPATH('')),1,1,'') as [Item] FROM [ItemMaster]

Another aproach that I would do as well if not finding previous reference is to store the row values into a variable using COALESCE in SQL.

DECLARE @str VARCHAR(100)SELECT @str = COALESCE(@str + ', ', '') + [Item]FROM [ItemMaster]

SELECT Item= @str