使用动态列构建 SQL 查询

2022-01-22 00:00:00 pivot sql sql-server unpivot

我的表格如下:

患者表

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?请帮忙.

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

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

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

推荐答案

这种类型的数据转换需要使用 pivot 和 unpivot 函数来完成.由于您的访问将是未知的，因此您将需要使用动态 sql.但首先，我将向您展示如何使用硬编码的值构建查询，以便更容易理解该过程的工作原理.

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.

首先，您需要对 date 和 weight 列进行 UNPIVOT，以便这些值在同一列中.这可以使用 UNION ALL 查询或 unpivot 函数来完成:

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:

反透视:

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() 应用于记录，这样您就可以知道每次访问的值:

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 函数应用于 col 列中的项目，但首先我们需要更改名称，以便它为您提供您想要的名称想.

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.

为此，我稍微更改了 SELECT 语句以将行号添加到列名:

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

到 PIVOT 如果您对值进行硬编码，您的查询将如下所示:

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

参见SQL Fiddle with Demo.

动态枢轴:

现在我已经解释了如何设置背后的逻辑，您将希望使用动态 sql 实现相同的过程.您的动态 sql 版本将是:

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)

参见 SQL Fiddle with Demo

两个版本的结果是:

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

相关文章