Click here to Skip to main content
15,868,141 members
Articles / Programming Languages / SQL
Tip/Trick

Transform column values into a row

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
13 May 2012CPOL 7.2K   36   3  
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.

SQL
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

SQL
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)


Written By
Software Developer
Sri Lanka Sri Lanka
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --