Putting comma separated values into separate or different columns

Question

I have a table and values as shown here

create table #example(id int primary key identity, cols varchar(255))
insert into #example(cols) values('HI,HELLO,BYE,TC')
insert into #example(cols) values('WHAT,ARE,YOU,DOING,HERE')

I need the resultant output as shown in the picture

Note : There is no limit for values i.e dynamic

create 2 tables and you´re having a more easy time doing this... — SomeJavaGuy, Apr 20 '17 at 05:39
https://sqlperformance.com/2012/07/t-sql-queries/split-strings — Hadi, Apr 20 '17 at 06:07
Split to rows, then pivot. Next time don't make the same mistake of using a single column to store multiple items. — Zohar Peled, Apr 20 '17 at 06:10
As Zohar suggests, this is more naturally modelled as multiple *rows*. If you have to pivot such dynamic data, it's usually best left to the presentation layer (application/report builder). You ought to recognize a fundamental truth - SQL is not a spreadsheet and rows and columns are fundamentally different. The natural representation that all of the tooling pushes you towards is a fixed set of columns and a variable number of rows. — Damien_The_Unbeliever, Apr 20 '17 at 06:59

Shakeer Mirza · Accepted Answer · 2017-04-20T06:50:14.903

This is what you are expecting

Schema:

CREATE TABLE #EXAMPLE(ID INT PRIMARY KEY IDENTITY, COLS VARCHAR(255))
INSERT INTO #EXAMPLE(COLS) VALUES('HI,HELLO,BYE,TC')
INSERT INTO #EXAMPLE(COLS) VALUES('WHAT,ARE,YOU,DOING,HERE')

Do split those comma separated values into Rows and Apply pivot

SELECT * FROM (
SELECT id
, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT 1)) AS ID2
, SPLT.CLMS.value('.','VARCHAR(MAX)') AS LIST FROM ( 
select id
, CAST( '<M>'+REPLACE(cols,',','</M><M>')+'</M>' AS XML) AS XML_COL from #example E
)E
CROSS APPLY  E.XML_COL.nodes('/M') AS SPLT(CLMS)
)A
PIVOT
(
    MAX(LIST) FOR ID2 IN ([1],[2],[3],[4],[5])
)PV

You will get result as

+----+------+-------+-----+-------+------+
| id |  1   |   2   |  3  |   4   |  5   |
+----+------+-------+-----+-------+------+
|  1 | HI   | HELLO | BYE | TC    | NULL |
|  2 | WHAT | ARE   | YOU | DOING | HERE |
+----+------+-------+-----+-------+------+

Edit:

And now you need to go for dynamic pivot,since There is no limit for values.

DECLARE @COLS VARCHAR(MAX)='', @QRY VARCHAR(MAX)='';


SELECT @COLS =@COLS+'['+CAST( ID2 AS VARCHAR(10))+'],' FROM (
SELECT  DISTINCT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT 1)) AS ID2  FROM ( 
select id, CAST( '<M>'+REPLACE(cols,',','</M><M>')+'</M>' AS XML) AS XML_COL from #example E
)E
CROSS APPLY  E.XML_COL.nodes('/M') AS SPLT(CLMS)
)A

SELECT @COLS = LEFT(@COLS,LEN(@COLS)-1)


SELECT @QRY =
'
SELECT * FROM (
SELECT id
, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT 1)) AS ID2
, SPLT.CLMS.value(''.'',''VARCHAR(MAX)'') AS LIST FROM ( 
select id, CAST( ''<M>''+REPLACE(cols,'','',''</M><M>'')+''</M>'' AS XML) AS XML_COL from #example E
)E
CROSS APPLY  E.XML_COL.nodes(''/M'') AS SPLT(CLMS)
)A
PIVOT
(
    MAX(LIST) FOR ID2 IN ('+@COLS+ ')
)PV'

EXEC( @QRY)

That's almost correct. Problem is the pivot must be dynamic since the OP stated that there is no limit on the number of items in the source column. — Zohar Peled, Apr 20 '17 at 06:30
I don't need to check it. I'm sure it works fine for the sample data provided. However, it will not be applicable if you add a row with 6 or more values. — Zohar Peled, Apr 20 '17 at 06:40

score 1 · Answer 2 · answered Apr 20 '17 at 06:55

Dynamic version base on split string then pivot table

Schema:

create table #example(id int primary key identity, cols varchar(255))
insert into #example(cols) values('HI,HELLO,BYE,TC')
insert into #example(cols) values('WHAT,ARE,YOU,DOING,HERE')

Calculate columns pivot

-- Calculate dynamic columns
;WITH temps AS 
(
  SELECT sd.* ,CAST('<x>' + replace(sd.cols, ',', '</x><x>') + '</x>' as xml) AS xmlText
  FROM #example sd
),
temps1 AS
(
   SELECT t.Id, t.cols, v.x.value('.','varchar(50)') AS Value
   FROM temps t
   CROSS APPLY 
     t.xmlText.nodes('/x') AS v(x)
)
SELECT @ColumnPivot =  STUFF((SELECT DISTINCT CONCAT(',COL',row_number() OVER(PARTITION BY t.id ORDER BY t.Value)) FROM temps1 t FOR XML PATH('')), 1,1,'')

PRINT @ColumnPivot

Then PIVOT table

 DECLARE @query nvarchar(max) = 
   CONCAT(N';WITH temps AS 
   (
      SELECT sd.* ,CAST(''<x>'' + replace(sd.cols, '','', ''</x><x>'') + ''</x>'' as xml) AS xmlText
      FROM #example sd
   ),
   temps1 AS
   (
      SELECT t.Id, t.cols, v.x.value(''.'' , ''varchar(50)'') AS Value
      FROM temps t
      CROSS APPLY 
         t.xmlText.nodes(''/x'') AS v(x)
   ),
   temps2 AS
   (
      SELECT t.* , CONCAT(''COL'',row_number() OVER(PARTITION BY t.id ORDER BY (select 1))) AS ColGroup
      FROM temps1 t
   )',
    N'SELECT Id, cols, ' , @ColumnPiVot,
  ' FROM
    (
       select * from temps2
    ) AS src
  PIVOT 
    (MAX(Value) FOR ColGroup IN (',@ColumnPiVot,')) AS pvt')
  PRINT @query
  exec sp_executesql  @query

DROP TABLE #example

Demo link: Rextester

Chanukya · Answer 3 · 2017-04-20T06:33:42.597

          DECLARE @COLS AS NVARCHAR(MAX),
    @QUERY  AS NVARCHAR(MAX);

SET @COLS = STUFF((SELECT DISTINCT ',' + QUOTENAME(C.RN) 
            FROM (SELECT ID,TOKEN,COLS
    ,ROW_NUMBER() OVER (
        PARTITION BY ID ORDER BY (
                SELECT NULL
                )
        ) AS RN
FROM #EXAMPLE
CROSS APPLY (
    SELECT *
    FROM UDF_SPLITSTRING(COLS, ',')
    ) A) C
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @QUERY = 'SELECT ID,cols, ' + @COLS + ' FROM 
            (
                SELECT ID,TOKEN,cols
    ,ROW_NUMBER() OVER (
        PARTITION BY ID ORDER BY (
                SELECT NULL
                )
        ) AS RN
FROM #EXAMPLE
CROSS APPLY (
    SELECT *
    FROM UDF_SPLITSTRING(COLS, '','')
    ) A

           ) X
            PIVOT 
            (
                 MAX(TOKEN)
                FOR rn IN (' + @COLS + ')
            ) P '


exec(@QUERY)

output

ID  cols                    1       2       3    4       5
1   HI,HELLO,BYE,TC         HI     HELLO    BYE  TC      NULL
2   WHAT,ARE,YOU,DOING,HERE WHAT    ARE     YOU  DOING   HERE

Putting comma separated values into separate or different columns

3 Answers3

Linked