Dec 3, 2011

Convert Rows to Column

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:
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:

2 comments:

  1. how to get distinct mailing account in the above case

    ReplyDelete
    Replies
    1. Sorry for the late reply Bhavika !

      You just need to add DISTINCT in your select query
      e.g.

      DECLARE @eMailList nvarchar(max)

      SELECT @eMailList = COALESCE(@eMailList + ';', '') +
      CAST(Email AS nvarchar(max))
      FROM #EmployeesEmailList

      Select DISTINCT @eMailList as eMailList

      Delete

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.