Rows to Column with For XML Path–Code



--Use this:
 ... FOR XML PATH(''), type).value('.','varchar(max)'))

 create table mytable (A INT,B INT,C char(1),D varchar(50))

insert into mytable values
(1,0,'X','ABC')
,(2,0,'X','DEF')
,(1,1,'Y','XYZ')
,(2,1,'Y','GHI')
,(3,1,'Y','MNO')
,(1,0,'A','OPQ')
,(2,0,'A','RST')

;with mycte as
(
Select Max(A) Over(Partition by b,c) A, B, C,D,Rank() Over(Order By B,C) rn from mytable)
SELECT DISTINCT A,B,C, Ds = STUFF((SELECT ',' + D
 FROM mycte m
 WHERE m.B = m1.B AND m.C = m1.C
 FOR XML PATH('')), 1, 1, '')
 FROM mycte m1;
drop table mytable

--Another sample:
create table mytable2 ( col varchar(50))

insert into mytable2 values
( 'a'),( 'b'),( 'c'),( 'd'),( 'e')

SELECT Distinct Cols = STUFF((
SELECT ',' + col
 FROM mytable2 m
 FOR XML PATH('')), 1, 1, '')
 FROM mytable2 m1;

drop table mytable2
/*

Cols
a,b,c,d,e

*/

--More example</pre>
create table test (id int, col varchar(50))
INSERT test VALUES (1, '<a>'), (1, '<href>'),(1, '<nbsp&;>'), (2, 'John'), (2, 'Tom'),(2, 'Bill>')

--Replace function to handle XML special elements (2008)
SELECT REPLACE(REPLACE(REPLACE(STUFF(( SELECT [text()]= ',' + ISNULL(col, '') + ''
FROM test
FOR XML PATH('')), 1,1, ''), N'&lt;', N'<'), N'&gt;', N'>'),N'&amp;',N'&')

--RBarryYoung (2009) from the comment part of the below blog

--... FOR XML PATH(''), type).value('.','varchar(max)'))

SELECT stuff((SELECT ','+ col FROM (select col from test) r FOR XML PATH(''), TYPE).value('.[1]','varchar(max)'),1,1,'')

--http://blogs.technet.com/b/wardpond/archive/2008/03/15/database-programming-the-string-concatenation-xml-trick-revisited-or-adam-is-right-but-we-can-fix-it.aspx

drop table test

--Summary of multiple ways to concatenate rows with T-SQL. (2008)
--https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
<pre>

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s