SQL：将个人的不同诊断放入水平行中

我正在为一家心理健康组织使用 Microsoft SQL Server 2008。

我有一个表列出了所有客户及其诊断，但客户的每个诊断都位于新行中。我希望它们全部排在一行中，水平列出，并附有每次诊断的日期。有些人只有一项诊断，有些人有 20 项诊断，有些人则没有。

Here's an example of how my data sort of looks now (only with a lot few clients, we have thousands):

And Here's the format I'd like it to end up:

您能提供的任何解决方案或正确方向的提示都会很棒，谢谢！

为了获得结果，我将首先对数据进行逆透视，然后再进行透视。这unpivot将获取您的日期和诊断列并将它们转换为行。一旦数据成行，您就可以应用数据透视表。

如果您有已知数量的值，那么您可以对查询进行硬编码，如下所示：

select *
from
(
  select person, [case#], age,
    col+'_'+cast(rn as varchar(10)) col,
    value
  from
  (
    select person, 
      [case#],
      age,
      diagnosis,
      convert(varchar(10), diagnosisdate, 101) diagnosisDate,
      row_number() over(partition by person, [case#]
                        order by DiagnosisDate) rn
    from yourtable
  ) d
  cross apply
  (
    values ('diagnosis', diagnosis), ('diagnosisDate', diagnosisDate)
  ) c (col, value)
) t
pivot
(
  max(value)
  for col in (diagnosis_1, diagnosisDate_1,
              diagnosis_2, diagnosisDate_2,
              diagnosis_3, diagnosisDate_3,
              diagnosis_4, diagnosisDate_4)

) piv;

See SQL 摆弄演示 http://www.sqlfiddle.com/#!3/c87f0/7.

我假设每个病例都有未知数量的诊断值。如果是这种情况，那么您将需要使用动态 sql 来生成结果：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT  ',' + QUOTENAME(col+'_'+cast(rn as varchar(10))) 
                    from 
                    (
                      select row_number() over(partition by person, [case#]
                                                order by DiagnosisDate) rn
                      from yourtable
                    ) t
                    cross join 
                    (
                      select 'Diagnosis' col union all 
                      select 'DiagnosisDate'
                    ) c
                    group by col, rn
                    order by rn, col
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT person, 
                    [case#],
                    age,' + @cols + '  
             from
             (
                select person, [case#], age,
                  col+''_''+cast(rn as varchar(10)) col,
                  value
                from
                (
                  select person, 
                    [case#],
                    age,
                    diagnosis,
                    convert(varchar(10), diagnosisdate, 101) diagnosisDate,
                    row_number() over(partition by person, [case#]
                                      order by DiagnosisDate) rn
                  from yourtable
                ) d
                cross apply
                (
                  values (''diagnosis'', diagnosis), (''diagnosisDate'', diagnosisDate)
                ) c (col, value)
            ) t
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query);

See SQL 摆弄演示 http://www.sqlfiddle.com/#!3/c87f0/14。两个查询都给出结果：

| PERSON |  CASE# | AGE |   DIAGNOSIS_1 | DIAGNOSISDATE_1 |      DIAGNOSIS_2 | DIAGNOSISDATE_2 |        DIAGNOSIS_3 | DIAGNOSISDATE_3 |  DIAGNOSIS_4 | DIAGNOSISDATE_4 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   John |  13784 |  56 |    Depression |      03/13/2012 |     Brain Injury |      03/14/2012 | Spinal Cord Injury |      03/15/2012 | Hypertension |      03/16/2012 |
|   Kate |   2643 |  37 |       Bipolar |      03/11/2012 |     Hypertension |      03/12/2012 |             (null) |          (null) |       (null) |          (null) |
|  Kevin | 500934 |  25 | Down Syndrome |      03/18/2012 | Clinical Obesity |      03/19/2012 |             (null) |          (null) |       (null) |          (null) |
|   Pete | 803342 |  34 |  Schizophenia |      03/17/2012 |           (null) |          (null) |             (null) |          (null) |       (null) |          (null) |