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

Answer 1

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)

Answer 2

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