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

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

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