SQL:将个体不同的诊断放在水平的一行中 [英] SQL: Putting an individuals distinct diagnosis into one horizontal row
问题描述
我正在将Microsoft SQL Server 2008用于精神卫生组织.
I'm using Microsoft SQL Server 2008 for a mental health organization.
我有一个表，列出了所有外出客户及其诊断，但是每一个客户具有的诊断都在新行中.我希望它们全部都在一行中，水平列出每个诊断的日期.有些人只有一种诊断，有些只有20种，有些没有.
I have a table that lists all of out clients and their diagnoses, but each diagnoses that a client has is in a new row. I want them all to be in a single row listed out horizontally with the date for each diagnosis. Some people have just one diagnosis, some have 20, some have none.
下面是一个示例，说明我的数据现在看起来如何(只有少数客户端，我们有数千个):
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:
谢谢您提供的任何解决方案或正确方向的提示，
Any solutions you could offer or hints in the right direction would be great, thanks!
推荐答案
为了获得结果，我首先取消透视，然后透视数据. unpivot 将获取您的日期和诊断列，并将其转换为行.一旦数据成行，就可以应用数据透视.
In order to get the result, I would first unpivot and then pivot your data. The unpivot will take your date and diagnosis columns and convert them into rows. Once the data is in rows, then you can apply the pivot.
如果您拥有已知数量的值，则可以像下面这样对查询进行硬编码:
If you have a known number of values, then you can hard-code your query similar to this:
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小提琴.
我将假设每种情况下的诊断值数量均未知.如果是这种情况，那么您将需要使用动态sql来生成结果:
I am going to assume that you will have an unknown number of diagnosis values for each case. If that is the case, then you will need to use dynamic sql to generate the result:
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小提琴.这两个查询都给出结果:
See SQL Fiddle with Demo. Both queries give the result:
| 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) |
这篇关于SQL:将个体不同的诊断放在水平的一行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
