Convert Rows to Column using COALESCE() function | SansSQL

Friday, November 25, 2011

Convert Rows to Column using COALESCE() function

Using the below query we can convert the rows to column sperated by a delimiter.
In the query I am using ';' as the delimiter and you can change the delimiter of your choice by replacing ';'.
Data from Table:
Query:
1
2
3
4
5
6
7
8
9
Use AdventureWorks2008R2
GO
DECLARE @eMailList nvarchar(max)
 
SELECT @eMailList = COALESCE(@eMailList + ';', '') +
   CAST(eMail AS nvarchar(max))
FROM Employees
 
Select @eMailList as eMailList

Output of the above Query:

Ads