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

Use of COALESCE

The purpose of COALESCE function in MS-SQL is to return nonnull expression within given arguments. In an example below the result will be the value that is not null and is found first within the given arguments (attributes in the table).

USE AdventureWorks ;
GO
SELECT Name, Class, Color, ProductNumber, COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product ;
GO
RESULTS:
If Class and Color Values are null and ProductNumber is '123' it will print '123' As FirstNotNull.

We can use Coalesce for Pivot also. Run the below command in Adventureworks database:

SELECT Name FROM HumanResources.Department WHERE (GroupName = 'Executive General and Administration')

Here we will get the standard Result like this:

Now if we want to Pivot the data, we can use like this:

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';' 
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

and get the following result set.