SQL: How to create columns dynamically

Question

I have a table which is created dynamically. So the number of columns is unknown at the time of creation. I want to create copies of each column in the same table with first column holding the first part of comma separated value, second column the second part and so on

For example,

  ID Value1 Value2  .... Valuen
  1  1;2;3  4;5;6      
  2  A;B;C  D;E;F

I want to get the output like

  ID Value1Copy1 Value1Copy2 Value1Copy3 Value2Copy1 Value2Copy2 Value2Copy3    .... ValuenCopy1
  1  1           2           3           4           5           6
  2  A           B           C           D           E           F

I am unable to achieve this for variable number of columns

"I have a table with unknown number of columns." A table in SQL Server has a known number of columns. — Gordon Linoff, Apr 24 '17 at 16:49
@Hassam I think what you actually mean is "lot of columns" instead of "unknown number of columns." — India.Rocket, Apr 24 '17 at 16:59

John Cappelletti · Accepted Answer · 2017-04-24T17:28:14.737

The following will dynamically unpivot your data. You may notice that the only field specified is ID.

The results are dropped into a #Temp table. From there we perform a dynamic pivot

Example

Declare @YourTable table (ID int,Value1 varchar(50),Value2 varchar(50))
Insert Into @YourTable values
(  1,  '1;2;3','4;5;6'),
(  2,  'A;B;C','D;E;F')

Select A.ID
      ,Col   = concat(C.Item,'Copy',D.RetSeq)
      ,Value = D.RetVal
 Into  #Temp
 From  @YourTable A     --<< Replace with Your actual table
 Cross Apply (Select XMLData = cast((Select A.* For XML Raw) as xml ) ) B       
 Cross Apply (
                Select Item   = attr.value('local-name(.)','varchar(100)')
                      ,Value  = attr.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row') as A(r)
                 Cross Apply A.r.nodes('./@*') AS B(attr)
                 Where attr.value('local-name(.)','varchar(100)') not in ('ID','Other2Exclude')
             ) C
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(C.Value,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) D
 Where A.ID is not null -- or any other WHERE statement

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(Col) From #Temp  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select *
 From  #Temp
 Pivot (max(Value) For [Col] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

SQL: How to create columns dynamically

1 Answers1