Create a SQL query with dynamic columns

This type of data transformation must be done with both functions pivot

and with unpivot

. Since your visits will be unknown, you will need to use dynamic sql. But first, I'll show you how to build a query with hard-coded values ​​to make it easier to understand how this process works.

First you need unpivot

columns date

and weight

so that the values ​​are in the same column. This can be done with a query UNION ALL

or the univot function:

UNPIVOT:

select patientid, name, rn, col, value
from
(
  select p.patientid, p.name, convert(char(5), v.date, 110) date, 
    cast(v.weight as char(5)) weight,
    row_number() over(partition by PatientID_FK order by date) rn
  from patients p
  left join visits v
    on p.patientid = v.PatientID_FK
) src
unpivot
(
  value
  for col in (date, weight)
) unpiv

      

        
        
        
      

    

See SQL Fiddle with Demo . The result of this query places the date and weight column values ​​in a single column with multiple rows. Note that I applied row_number()

to the records so you can tell what values ​​are coming with each visit:

| PATIENTID |  NAME | RN |    COL | VALUE |
-------------------------------------------
|         1 | James |  1 |   date | 01-01 |
|         1 | James |  1 | weight | 220   |
|         1 | James |  2 |   date | 02-01 |
|         1 | James |  2 | weight | 210   |

      

        
        
        
      

    

PIVOT:

The next step is to apply the function pivot

to the items in the column col

, but first we need to change the name so that it gives you the names you want.

To do this, I modify the statement slightly SELECT

to add the line number to the col name:

select patientid, name, 'Visit'+col + cast(rn as varchar(10)) new_col, 
  value
from ...

      

        
        
        
      

    

This will give you new names, which are the names you want to use as columns:

Visitdate1 
Visitweight1
Visitdate2
Visitweight2

      

        
        
        
      

    

For pivot

data, your query will look like this if you adjust the values:

select *
from
(
  select patientid, name, 'Visit'+col + cast(rn as varchar(10)) new_col, 
    value
  from
  (
    select p.patientid, p.name, convert(char(5), v.date, 110) date, 
      cast(v.weight as char(5)) weight,
      row_number() over(partition by PatientID_FK order by date) rn
    from patients p
    left join visits v
      on p.patientid = v.PatientID_FK
  ) src
  unpivot
  (
    value
    for col in (date, weight)
  ) unpiv
) s1
pivot
(
  max(value)
  for new_col in (Visitdate1,Visitweight1,
                  Visitdate2,Visitweight2)
) piv

      

        
        
        
      

    

See SQL Fiddle with Demo .

Dynamic PIVOT:

Now that I've explained the logic behind how this is set up, you will want to implement this same process using dynamic sql. The dynamic sql version would be:

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

select @colsUnpivot = stuff((select ', '+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('visits') and
               C.name not in ('PatientID_FK')
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' + quotename('Visit'+c.name 
                                          + cast(v.rn as varchar(10)))
                    from
                    (
                       select row_number() over(partition by PatientID_FK order by date) rn
                       from visits
                    ) v
                    cross apply sys.columns as C
                   where C.object_id = object_id('visits') and
                     C.name not in ('PatientID_FK')
                   group by c.name, v.rn
                   order by v.rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select *
      from
      (
        select patientid, name, ''Visit''+col + cast(rn as varchar(10)) new_col,
          value
        from 
        (
          select p.patientid, p.name, convert(char(5), v.date, 110) date, 
            cast(v.weight as char(5)) weight,
            row_number() over(partition by PatientID_FK order by date) rn
          from patients p
          left join visits v
            on p.patientid = v.PatientID_FK
        ) x
        unpivot
        (
          value
          for col in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        max(value)
        for new_col in ('+ @colspivot +')
      ) p'

exec(@query)

      

        
        
        
      

    

See SQL Fiddle with Demo

Result from both versions:

| PATIENTID |  NAME | VISITDATE1 | VISITWEIGHT1 | VISITDATE2 | VISITWEIGHT2 |
-----------------------------------------------------------------------------
|         1 | James |      01-01 |        220   |      02-01 |        210   |