SQL：将个体不同的诊断放在水平的一行中

一尘不染

SQL：将个体不同的诊断放在水平的一行中

sql

我正在将Microsoft SQL Server 2008用于精神卫生组织。

我有一个表，列出了所有外出的客户端及其诊断，但是每个客户端具有的诊断都在新行中。我希望它们全部都在一行中，水平列出每个诊断的日期。有些人只有一种诊断，有些只有20种，有些没有。

您能提供的任何解决方案或正确方向的提示都将非常有用，谢谢！

阅读 187

2021-05-23

共1个答案

一尘不染

为了获得结果，我将首先取消透视，然后再透视您的数据。该 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;

请参阅带有演示的SQL Fiddle。

我将假设每种情况下的诊断值数量都是未知的。如果是这种情况，那么您将需要使用动态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);

请参阅带有演示的SQL Fiddle。这两个查询都给出结果：

| 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) |

2021-05-23