我的表如下:

患者表

PatientId   Name
1           James
...

访问表
Date    PatientID_FK    Weight
1/1     1               220
2/1     1               210
...

如何建立返回的查询
PatientId    Name    Visit1Date    Visit1Weight    Visit2Date    Visit2Weight    ...
1            James   1/1           220             2/1           210
2            ...

我们如何以这种方式添加更多列?如何写那个SELECT?请帮忙。

StackExchange上的一些帖子说,SQL语句无法处理它。真的是这样吗

最佳答案

这种类型的数据转换将需要同时使用pivotunpivot函数来完成。由于您的访问将是未知的,因此您将要使用动态sql。但是首先,我将向您展示如何使用硬编码的值构建查询,以便更轻松地了解流程的工作方式。

首先,您需要对UNPIVOTdate列进行weight,以便将值放在同一列中。这可以使用UNION ALL查询或unpivot函数完成:

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 with Demo。该查询的结果将date和weight列的值都放入具有多行的单个列中。请注意,我在记录中应用了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:

下一步是将PIVOT函数应用于col列中的项目,但是首先我们需要更改名称,以便为您提供所需的名称。

为此,我会稍微更改SELECT语句,以将行号添加到col名称:
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 with Demo

动态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 with Demo

这两个版本的结果是:
| PATIENTID |  NAME | VISITDATE1 | VISITWEIGHT1 | VISITDATE2 | VISITWEIGHT2 |
-----------------------------------------------------------------------------
|         1 | James |      01-01 |        220   |      02-01 |        210   |

07-24 09:37
查看更多