How to make dynamic column

Question

I have some data as under

Declare @t table (Id int identity,CommaSeperatedValue varchar(100))
Insert Into @t 
Select 'Somalia,Vietnam' Union All 
Select 'apple,banana,guava,India,Australia'

There is no limit in the CommaSeperated value. The desired output for the sample provided will be

Id  Col1    Col2    Col3    Col4    Col5
1   Somalia Vietnam Null    Null    Null
2   apple   banana  guava   India   Australia

That means , the columns will be generated dynamically. Let us take another example

Declare @t table (Id int identity,CommaSeperatedValue varchar(100))
Insert Into @t 
Select 'Somalia,Vietnam,Honolulu,Spain' Union All 
Select 'apple,banana,guava,India,Australia,Smart,Bus' Union All
Select 'Mango'

The desired output

Id  Col1    Col2    Col3    Col4    Col5        Col6    Col7    
1   Somalia Vietnam Honolulu Spain  Null        Null    Null
2   apple   banana  guava   India   Australia   Smart   Bus
3   Mango   Null    Null    Null    Null        Null    Null

How to do this query?

My attempt so far(after this I am lost)

SELECT 
    X.id,
    X.CommaSeperatedValue,
    Y.splitdata 
FROM
 (
    SELECT *,
    CAST('<X>'+REPLACE(F.CommaSeperatedValue,',','</X><X>')+'</X>' AS XML) AS xmlfilter 
    FROM @t F
 )X
 CROSS APPLY
 ( 
    SELECT fdata.D.value('.','varchar(50)') as splitdata 
    FROM X.xmlfilter.nodes('X') as fdata(D)
 )Y

Thanks in advance

Answer 1

Well here is the Dynamic Solution you are looking for .I used Temp Table you can replace it with Permanent Table or Table Variable.

Declare @t table (Id int identity,CommaSeperatedValue varchar(100))
Insert Into @t 
Select 'Somalia,Vietnam' Union All 
Select 'apple,banana,guava,India,Australia'
IF object_ID('TempDB..#Temp') IS NOT NULL DROP TABLE #Temp;
SELECT 
X.id,
--X.CommaSeperatedValue,
Y.splitdata
,ROW_NUMBER() OVER( PARTITION  BY X.id ORDER BY X.id ) AS DataID
INTO #Temp 
FROM
 (
SELECT *,
CAST('<X>'+REPLACE(F.CommaSeperatedValue,',','</X><X>')+'</X>' AS XML) AS xmlfilter 
FROM @t F
 )X
 CROSS APPLY
 ( 
    SELECT fdata.D.value('.','varchar(50)') as splitdata 
    FROM X.xmlfilter.nodes('X') as fdata(D)
 )Y

DECLARE @MAXCol INT = (SELECT MAX(DataID)FROM #Temp)
     ,@index INT =1
     ,@ColNames varchar(4000)=''
     WHILE (@index<=@MAXCol)
     BEGIN 
     SET @ColNames =@ColNames +'MAX(CASE WHEN DataID = '+LTRIM(STR(@index))+' THEN splitdata END) as Col'+LTRIM(STR(@index))+','
     SET @Index=@Index +1        
     END
SET @ColNames = LEFT(@ColNames,LEN(@ColNames)-1) -- Remove Last Comma
EXECUTE   ( 'SELECT
        [id],'+@ColNames+' FROM #Temp GROUP BY [id]'
      )

Answer 2

Going with what you already got, add a rownumber, and transpose it using a group by.

SELECT
    [id],
    MAX(CASE WHEN RowNumber=1 THEN splitdata END) as Col1,
    MAX(CASE WHEN RowNumber=2 THEN splitdata END) as Col2,
    MAX(CASE WHEN RowNumber=3 THEN splitdata END) as Col3,
    MAX(CASE WHEN RowNumber=4 THEN splitdata END) as Col4,
    MAX(CASE WHEN RowNumber=5 THEN splitdata END) as Col5,
    MAX(CASE WHEN RowNumber=6 THEN splitdata END) as Col6,
    MAX(CASE WHEN RowNumber=7 THEN splitdata END) as Col7,
    MAX(CASE WHEN RowNumber=8 THEN splitdata END) as Col8,
    MAX(CASE WHEN RowNumber=9 THEN splitdata END) as Col9,
    MAX(CASE WHEN RowNumber=10 THEN splitdata END) as Col10

FROM (
    SELECT 
        X.id,
        Y.splitdata,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS RowNumber
    FROM
     (
        SELECT *,
        CAST('<X>'+REPLACE(F.CommaSeperatedValue,',','</X><X>')+'</X>' AS XML) AS xmlfilter 
        FROM @t F
     )X
     CROSS APPLY
     ( 
        SELECT fdata.D.value('.','varchar(50)') as splitdata 
        FROM X.xmlfilter.nodes('X') as fdata(D)
     )Y
) X
GROUP BY [id]

This will yield:

id  Col1    Col2    Col3    Col4    Col5    Col6    Col7    Col8    Col9    Col10
1   Somalia Vietnam Honolulu    Spain   NULL    NULL    NULL    NULL    NULL    NULL
2   apple   banana  guava   India   Australia   Smart   Bus NULL    NULL    NULL
3   Mango   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

Answer 3

I believe you would need to use dynamic SQL to do this, because the SELECT statement needs to specify the number of columns, or you need to iterate over the number of items in the CSV column.

Typically this is a bad idea, will cause more problems than it solves, and is too confusing for the next person to maintain. What you might want to consider instead is simply flattening your data to a two columned format

-- ie your initial data
1, 'Somalia,Vietnam,Honolulu,Spain'
2, 'apple,banana,guava,India,Australia,Smart,Bus'
3, 'Mango'

-- would become
1, 'Somalia'
1, 'Vietnam'
1, 'Honolulu'
1, 'Spain'
2, 'apple'
2, 'banana
-- etc 

Now group and pivot in your presentation layer.

Trying to format data in SQL (probably for a report or data export?) is a common mistake, really just one up from trying to store dates as literals. SQL is a data storage and manipulation language / platform; it is not for data Trying to use it in this manner will simply cause yourself no end of headache.