.

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

What's your thoughts on this?

*

Protected by WP Anti Spam