Transform column values into a row

• Download source - 497 B

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