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