Monday, December 19, 2011

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.


Items in table ItemMaster with :
ItemMaster  -> 

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

No comments:

Post a Comment