Построить SQL-запрос с динамическими столбцами

Этот тип преобразования данных необходимо будет выполнять с помощью функций pivot и unpivot. Поскольку ваши посещения будут неизвестны, вы захотите использовать динамический sql. Но сначала я покажу вам, как построить запрос с жестко закодированными значениями, чтобы было легче понять, как работает процесс.

Во-первых, вам нужно UNPIVOT столбцы date и weight, чтобы значения находились в одном столбце. Это можно сделать с помощью запроса UNION ALL или функции 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

См. SQL Fiddle с демонстрацией. Результат этого запроса помещает значения столбца даты и веса в один столбец с несколькими строками. Обратите внимание, что я применил к записям row_number(), чтобы вы могли сказать, какие значения соответствуют каждому посещению:

| 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 к элементам в столбце col, но сначала нам нужно изменить имя, чтобы оно давало вам нужные имена.

Для этого я немного изменяю оператор SELECT, добавляя номер строки к имени столбца:

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

Это даст вам новые имена, которые являются именами, которые вы хотите использовать в качестве столбцов:

Visitdate1 
Visitweight1
Visitdate2
Visitweight2

Для PIVOT данных ваш запрос будет выглядеть следующим образом, если вы жестко закодируете значения:

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

См. SQL Fiddle с демонстрацией.

Динамический PIVOT:

Теперь, когда я объяснил логику того, как это делается, вы захотите реализовать тот же процесс, используя динамический sql. Ваша динамическая версия sql будет:

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)

См. SQL Fiddle с демонстрацией.

Результат обеих версий:

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