所需的think数据透视帮助(我认为)

Question

我希望将表中1行的值作为查询中的列名，其中列的数据来自第二个表。我认为这可以通过一个支点来实现，但在试图解决这个问题上却毫无进展。

具体来说，我们希望记录不同抗原的抗体水平，随着时间的推移，病人。实际的抗原将被监测将不同的每个病人。因此，我们有一个记录将被监控的抗原的表，这个表被称为reftblDSAColumnLabels。正如您在示例中所看到的，有两行是由PersonCategoryId标识的，用于不同的人。这个表对PersonCategoryId有一个唯一的约束。

(该表实际上有多达“Antigen12Label”，因此最多可以监测每名病人的12种不同抗原的抗体水平，但我在这里简化了它)

​

​

包含数据的表名为tblDSAData，您可以看到PersonCategoryId =1的病人的一些有代表性的行

​

我想要实现的是具有以下列标题的输出，其中PersonCategoryId =1

人员

其中PersonCategoryId =2

人员

我觉得这应该是相当容易的，但我似乎有一个心理障碍，涉及到枢轴。

Answer 1

我看到的部分问题是，您有两个表是非规范化的，这意味着您基本上有两个表被设计为电子表格而不是表。解决此问题的最佳方法是重构表。

如果可能的话，我的建议是将表格结构改为：

CREATE TABLE reftblDSAColumnLabels
(
  [PersonCategoryId] int, 
  [AntigenNum] int, 
  [AntigenValue] varchar(4)
);

CREATE TABLE tblDSAData 
(
  [PersonCategoryId] int, 
  [SampleDate] datetime,
  [AntigenNum] int, 
  [AntigenValue] int
);

通过这种方式，您可以在personCategoryId和AntigenNum (1、2、3等)上加入表。你就会明白我为什么马上提出这个建议了。

由于您的表是非规范化的，因此很难通过传入personCategoryId来动态生成结果集。您将需要使用动态SQL来根据提交给过程的id生成结果。

为了得到这一结果，我建议同时应用UNPIVOT和枢轴函数。UNPIVOT将使用多列中的表，并将它们转换为我前面建议的结构。这将使获得结果更加容易。

UNPIVOT:

您需要取消两个表的枢轴，对unpivot的查询将类似于以下内容：

select personCategoryId, 
  replace(replace(col, 'Antigen', ''), 'Label', '') colNum,
  value l_value
from reftblDSAColumnLabels
unpivot
(
  value
  for col in ([Antigen1Label], [Antigen2Label], [Antigen3Label], [Antigen4Label])
) unpiv
where PersonCategoryId = 1;

请参阅与Demo

select personCategoryId,SampleDate,
  replace(replace(col, 'Antigen', ''), 'Value', '') colNum,
  value d_value
from tblDSAData
unpivot
(
  value
  for col in ([Antigen1Value], [Antigen2Value], [Antigen3 Value], [Antigen4Value])
) unpiv;

见与Demo。如果运行这些查询，您会注意到得到的结果与以下类似：

| PERSONCATEGORYID | COLNUM | L_VALUE |
---------------------------------------
|                1 |      1 |      A1 |
|                1 |      2 |     Cw6 |
|                1 |      3 |    DR15 |
|                1 |      4 |    DR51 |

和

| PERSONCATEGORYID |                      SAMPLEDATE | COLNUM | D_VALUE |
-------------------------------------------------------------------------
|                1 | February, 08 2013 00:00:00+0000 |      1 |    1278 |
|                1 | February, 08 2013 00:00:00+0000 |      2 |   11272 |
|                1 | February, 08 2013 00:00:00+0000 |      3 |    6880 |
|                1 | February, 08 2013 00:00:00+0000 |      4 |    7544 |
|                1 | February, 11 2013 00:00:00+0000 |      1 |    1711 |
|                1 | February, 11 2013 00:00:00+0000 |      2 |    9681 |
|                1 | February, 11 2013 00:00:00+0000 |      3 |    8437 |
|                1 | February, 11 2013 00:00:00+0000 |      4 |    8967 |

枢轴

一旦数据采用了这种多行格式，您就可以轻松地加入personCategoryId和colNum上的结果，并应用PIVOT函数来获得最终结果。具有联接和支点的代码将是：

select *
from
(
  select l.personCategoryId, l_value, d_value, SampleDate
  from
  (
    select personCategoryId, 
      replace(replace(col, 'Antigen', ''), 'Label', '') colNum,
      value l_value
    from reftblDSAColumnLabels
    unpivot
    (
      value
      for col in ([Antigen1Label], [Antigen2Label], [Antigen3Label], [Antigen4Label])
    ) unpiv
    where PersonCategoryId = 1
  ) l
  inner join
  (
    select personCategoryId,SampleDate,
      replace(replace(col, 'Antigen', ''), 'Value', '') colNum,
      value d_value
    from tblDSAData
    unpivot
    (
      value
      for col in ([Antigen1Value], [Antigen2Value], [Antigen3Value], [Antigen4Value])
    ) unpiv
  ) d
    on l.PersonCategoryId = d.PersonCategoryId
    and l.colNum = d.colNum
) src
pivot
(
  max(d_value)
  for l_value in (A1, Cw6, DR15, DR51)
) piv;

见与Demo。

现在，对于当前的问题，您需要传入personCategoryId，以便每个id的列标题都会发生更改。由于列标题将更改，因此需要使用动态SQL来获得结果。您可以轻松地将上述代码转换为动态SQL，脚本如下：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @personCategoryId int = 1

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(value) 
                    from
                    (
                      select value
                      from reftblDSAColumnLabels
                      unpivot
                      (
                        value
                        for col in ([Antigen1Label], [Antigen2Label], [Antigen3Label], [Antigen4Label])
                      ) unpiv
                      where PersonCategoryId = @personCategoryId
                    ) d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT personCategoryId, SampleDate,' + @cols + '  
             from
              (
                select l.personCategoryId, l_value, d_value, SampleDate
                from
                (
                  select personCategoryId, 
                    replace(replace(col, ''Antigen'', ''''), ''Label'', '''') colNum,
                    value l_value
                  from reftblDSAColumnLabels
                  unpivot
                  (
                    value
                    for col in ([Antigen1Label], [Antigen2Label], [Antigen3Label], [Antigen4Label])
                  ) unpiv
                  where PersonCategoryId = '+cast(@personCategoryId as varchar(10))+'
                ) l
                inner join
                (
                  select personCategoryId,SampleDate,
                    replace(replace(col, ''Antigen'', ''''), ''Value'', '''') colNum,
                    value d_value
                  from tblDSAData
                  unpivot
                  (
                    value
                    for col in ([Antigen1Value], [Antigen2Value], [Antigen3Value], [Antigen4Value])
                  ) unpiv
                ) d
                  on l.PersonCategoryId = d.PersonCategoryId
                  and l.colNum = d.colNum
            ) src
            pivot 
            (
                max(d_value)
                for l_value in (' + @cols + ')
            ) p '


execute(@query)

见与Demo。所有版本都会给出一个结果：

| PERSONCATEGORYID | SAMPLEDATE |   A1 |   CW6 |  DR15 | DR51 |
---------------------------------------------------------------
|                1 | 2013-02-08 | 1278 | 11272 |  6880 | 7544 |
|                1 | 2013-02-11 | 1711 |  9681 |  8437 | 8967 |
|                1 | 2013-02-13 | 2107 | 11516 |  8958 | 7884 |
|                1 | 2013-02-15 | 1947 | 13857 | 10352 | 8719 |
|                1 | 2013-02-18 | 1917 | 10026 |  9848 | 8493 |

编辑1，如果要使这两个表正常化，仍然需要使用dynamic来获取每个personCategoryId的列标题，但是您将能够删除两个表的不枢轴。守则将是：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @personCategoryId int = 1

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(AntigenValue) 
                    from reftblDSAColumnLabels
                    where PersonCategoryId = @personCategoryId
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT personCategoryId, SampleDate,' + @cols + '  
             from
              (
                select l.personCategoryId, d.SampleDate,
                    l.AntigenValue l_value, d.AntigenValue d_value
                from reftblDSAColumnLabels l
                inner join tblDSAData d
                  on l.PersonCategoryId = d.PersonCategoryId
                  and l.AntigenNum = d.AntigenNum
            ) src
            pivot 
            (
                max(d_value)
                for l_value in (' + @cols + ')
            ) p '


execute(@query)

请参阅与Demo