Build SQL query with dynamic columns

Question

My tables go as follows:

Patients table

PatientId   Name
1           James
...

Visits table

Date    PatientID_FK    Weight
1/1     1               220
2/1     1               210 
...

How can I build a query that returns

PatientId    Name    Visit1Date    Visit1Weight    Visit2Date    Visit2Weight    ...
1            James   1/1           220             2/1           210
2            ...

How can we add more columns in this way? How to write that SELECT? Please help.

---

Some posts on StackExchange say it is impossible for a SQL statement to handle it. Is it really so?

Answer 1

This type of data transformation will need to be done with both a pivot and the unpivot functions. Since your visits will be unknown, then you will want to use dynamic sql. But first, I will show you how to build the query with the values hard-coded so it makes it easier to understand how the process works.

First, you need to UNPIVOT the date and weight columns so the values are in the same column. This can be done using a UNION ALL query or the unpivot 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 values of both the date and weight column into a single column with multiple rows. Notice that I applied a row_number() to the records so you will be able to tell what values go 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 PIVOT function to the items in the col column, but first we need to alter the name so it gives you the names that you want.

To do that I alter the SELECT statement slightly to add the row number to the col name:

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

This will give you the new names which are the names that you want as columns:

Visitdate1 
Visitweight1
Visitdate2
Visitweight2

To PIVOT the data your query will look like the following if you hard-code 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 have explained the logic behind how this is set up, you will want to implement this same process using dynamic sql. You dynamic sql version will 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

The result from both versions is:

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