SQL to Make XML Path list Columns

Question

I have the following SQL Server query which cranks out a comma delimited list into one field.

Result looks like this 2003, 9083, 4567, 3214

Question: What would be the best way (SQL syntax) to put this into columns?

Meaning, I need these to show up as 1 column for "2003", 1 column for "9083" 1 column, for "4567" ..etc.
Obviously the number of columns would be dynamic based on the policy ID I give it . Any idea would be most appreciated.

My query is below .

SELECT DISTINCT x.ClassCode + ', '
FROM PremByClass x
WHERE x.PolicyId = 1673885
FOR XML PATH('')

score 1 · Answer 1 · edited May 23 '17 at 11:51

1

If you take out the XML and the comma you are left with

SELECT DISTINCT x.ClassCode
FROM PremByClass x
WHERE x.PolicyId = 1673885

Which gives you a single column of the values, to turn this into columns you need to PIVOT it. However, you need to specify the names of the columns.

There is some more information in this answer https://stackoverflow.com/a/15931734/350188

edited May 23 '17 at 11:51

Community

1
1

answered Sep 12 '16 at 13:24

Stephen Turner

7,125
4
51
68

score 1 · Answer 2 · answered Sep 12 '16 at 13:44

You need PIVOT and if number of values could be different - dynamic SQL:

SELECT *
FROM (
SELECT DISTINCT ClassCode,
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM PremByClass 
WHERE PolicyId = 1673885
) as t
PIVOT (
    MAX(ClassCode) FOR RN IN ([1],[2],[3],[4])
) as pvt

Will give you:

1       2       3       4
-----------------------------
2003    9083    4567    3214

Dynamic SQL will be something like:

DECLARE @sql nvarchar(max),
        @columns nvarchar(max)

SELECT @columns = STUFF((
    SELECT DISTINCT ','+QUOTENAME(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) 
    FROM PremByClass 
    WHERE PolicyId = 1673885
    FOR XML PATH('')
),1,1,'')

SELECT @sql = N'
SELECT *
FROM (
SELECT DISTINCT ClassCode,
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM PremByClass 
WHERE PolicyId = 1673885
) as t
PIVOT (
    MAX(ClassCode) FOR RN IN ('+@columns+')
) as pvt'

EXEC sp_executesql @sql

score 1 · Answer 3 · answered Sep 12 '16 at 14:28

Assuming that there's a limit to the amount of numbers in that csv string.
You could cast or convert it to an xml type, and then put the values in as many columns you expect.

In this example it's assumed that there's no more than 6 values in the text:

declare @PolicyId INT = 1673885;

select PolicyId
,x.value('/x[1]','int') as n1
,x.value('/x[2]','int') as n2
,x.value('/x[3]','int') as n3
,x.value('/x[4]','int') as n4
,x.value('/x[5]','int') as n5
,x.value('/x[6]','int') as n6
from (
  select 
  PolicyId, 
  cast('<x>'+replace(ClassCode,',','</x><x>')+'</x>' as xml) as x 
  from PremByClass
  where PolicyId = @PolicyId
) q;

SQL to Make XML Path list Columns

3 Answers3