Click here to Skip to main content
11,705,451 members (49,719 online)
Click here to Skip to main content

Transform column values into a row

, 13 May 2012 CPOL 3.4K 31 3
Rate this:
Please Sign up or sign in to vote.
The following article describes how to transform column values into rows

Introduction

The following script is useful in creating comma separated row values.

Background

Here, I have created a table called Sample which contains two columns as ID, Type and enter some data as follows:

Using the Code

Here, I have used STUFF() and XML PATH() functions.

SELECT S.ID, 

(SELECT  STUFF(( SELECT  DISTINCT'],[' + [TYPE] FROM [Sample] _
WHERE ID=S.ID  FOR XML PATH('')), 1, 2, '') + ']') AS [TYPE]

FROM [Sample] AS S
GROUP BY S.ID  

The result will appear as follows:

Points of Interest

SELECT  DISTINCT [TYPE] FROM [Sample] FOR XML PATH('') 

Using XML PATH() with select statement will yield result as follows:

STUFF() will replace the first two characters ( ],) with empty string.

History

  • May 01, 2012: Article created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Chamila Ranasinghe
Software Developer
Sri Lanka Sri Lanka
No Biography provided

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 14 May 2012
Article Copyright 2012 by Chamila Ranasinghe
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid