.

creating dynamic SQL tables using ‘pivot’ aka “cross tabulation of columns & values”

Need to keep on adding columns to a table in a dynamic way and store it in a database?

Here follows a prototype of the solution:

--drop table MyPasTable
create table MyPasTable
(
  SpellId int,
  ColName varchar(20),
  Value varchar(20)
);  

insert into MyPasTable values
(38753,   'PatientName',      'john'),
(38753,   'Surname',      'smith'),
(38753,   'S2',      '23'),
(32255,   'PatientName',     'jack'), 
(38755,   'S2',     '2154'), 
(38755,   'S5',     '154'), 
(38755,   'PatientName',    'pete')

DECLARE @colsPivot AS NVARCHAR(MAX),
    @colsUnpivot as NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT distinct ',' + quotename(ColName) 
                    from MyPasTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsUnpivot = stuff((select ','+quotename(C.Name)
         from sys.columns as C
         where C.object_id = object_id('MyPasTable') and
               C.name LIKE 'value'
         for xml path('')), 1, 1, '')

set @query 
  = 'select *
      from
      (
        select SpellId, ColName, val
        from MyPasTable
        unpivot
        (
          val
          for SpellIdx in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        max(val)
        for ColName in ('+ @colspivot +')
      ) p'

exec(@query)

drop table MyPasTable

That would result in the following output

SpellId PatientName S2 S5 Surname
32255 jack NULL NULL NULL
38753 john 23 NULL smith
38755 pete 2154 154 NULL