select PatientCode,PatientName, Item_A=case when ItemName='Item_A' then ExamResult else null end, Item_B=case when ItemName='Item_B' then ExamResult else null end, Item_C=case when ItemName='Item_C' then ExamResult else null end, Item_D=case when ItemName='Item_D' then ExamResult else null end, Summary FROM T_Patient A,T_Depart B,T_Item C WHERE A.ID_Patient=B.ID_Patient AND B.ID_Depart=C.ID_Depart
select PatientCode,PatientName, Item_A=MAX(case when ItemName='Item_A' then ExamResult else null end), Item_B=MAX(case when ItemName='Item_B' then ExamResult else null end), Item_C=MAX(case when ItemName='Item_C' then ExamResult else null end), Item_D=MAX(case when ItemName='Item_D' then ExamResult else null end), Summary=MAX(Summary) FROM #T_Patient A,#T_Depart B,#T_Item C WHERE A.ID_Patient=B.ID_Patient AND B.ID_Depart=C.ID_Depart GROUP BY PatientCode,PatientNamePatientCode PatientName Item_A Item_B Item_C Item_D Summary ----------- ----------- ------ ------ ------ ------ ------- 20100001 张三 结果1 结果2 结果3 结果4 无异常 20100002 李四 结果5 结果6 结果7 结果8 异常1 20100003 王五 结果9 结果10 结果11 结果12 异常2 20100004 陈六 结果13 结果14 结果15 结果16 异常3 警告: 聚合或其他 SET 操作消除了空值。
--> 测试数据:[T_Patient] if object_id('[T_Patient]') is not null drop table [T_Patient] create table [T_Patient]([ID_Patient] varchar(6),[PatientCode] int,[PatientName] varchar(4)) insert [T_Patient] select 'P_0001',20100001,'张三' union all select 'P_0002',20100002,'李四' union all select 'P_0003',20100003,'王五' union all select 'P_0004',20100004,'陈六' --> 测试数据:[T_Depart] if object_id('[T_Depart]') is not null drop table [T_Depart] create table [T_Depart]([ID_Depart] varchar(6),[DepartCode] varchar(8),[ID_Patient] varchar(6),[Summary] varchar(6)) insert [T_Depart] select 'D_0001','Depart01','P_0001','无异常' union all select 'D_0002','Depart01','P_0002','异常1' union all select 'D_0003','Depart01','P_0003','异常2' union all select 'D_0004','Depart01','P_0004','异常3' --> 测试数据:[T_Item] if object_id('[T_Item]') is not null drop table [T_Item] create table [T_Item]([ID_Item] varchar(6),[ID_Depart] varchar(6),[ItemName] varchar(6),[ExamResult] varchar(6)) insert [T_Item] select 'I_0001','D_0001','Item_A','结果1' union all select 'I_0002','D_0001','Item_B','结果2' union all select 'I_0003','D_0001','Item_C','结果3' union all select 'I_0004','D_0001','Item_D','结果4' union all select 'I_0005','D_0002','Item_A','结果5' union all select 'I_0006','D_0002','Item_B','结果6' union all select 'I_0007','D_0002','Item_C','结果7' union all select 'I_0008','D_0002','Item_D','结果8' union all select 'I_0009','D_0003','Item_A','结果9' union all select 'I_0010','D_0003','Item_B','结果10' union all select 'I_0011','D_0003','Item_C','结果11' union all select 'I_0012','D_0003','Item_D','结果12' union all select 'I_0013','D_0004','Item_A','结果13' union all select 'I_0014','D_0004','Item_B','结果14' union all select 'I_0015','D_0004','Item_C','结果15' union all select 'I_0016','D_0004','Item_D','结果16'select * from [T_Patient] select * from [T_Depart] select * from [T_Item]/* -PatientCode PatientName Item_A Item_B Item_C Item_D Summary 20100001 张三 结果1 结果2 结果3 结果4 无异常 20100002 李四 结果5 结果6 结果7 结果8 异常1 20100003 王五 结果9 结果10 结果11 结果12 异常2 20100004 陈六 结果13 结果14 结果15 结果16 异常3 */ select A.patientcode, A.patientname, [Item_A] = MAX(CASE WHEN C.[ItemName]='Item_A' THEN C.[ExamResult] ELSE '' END) , Item_B = MAX(CASE WHEN C.[ItemName]='Item_B' THEN C.[ExamResult] ELSE '' END) , Item_C = MAX(CASE WHEN C.[ItemName]='Item_C' THEN C.[ExamResult] ELSE '' END) , Item_D = MAX(CASE WHEN C.[ItemName]='Item_D' THEN C.[ExamResult] ELSE '' END) , B.Summary from [T_Patient] A LEFT JOIN [T_Depart] B ON A.[ID_Patient] = B.[ID_Patient] LEFT JOIN [T_Item] C ON B.[ID_Depart] = C.[ID_Depart] GROUP BY A.patientcode, A.patientname,B.Summary /* patientcode patientname Item_A Item_B Item_C Item_D Summary ----------- ----------- ------ ------ ------ ------ ------- 20100001 张三 结果1 结果2 结果3 结果4 无异常 20100002 李四 结果5 结果6 结果7 结果8 异常1 20100003 王五 结果9 结果10 结果11 结果12 异常2 20100004 陈六 结果13 结果14 结果15 结果16 异常3(4 行受影响)*/
Item_A=case when ItemName='Item_A' then ExamResult else null end,
Item_B=case when ItemName='Item_B' then ExamResult else null end,
Item_C=case when ItemName='Item_C' then ExamResult else null end,
Item_D=case when ItemName='Item_D' then ExamResult else null end,
Summary
FROM T_Patient A,T_Depart B,T_Item C
WHERE A.ID_Patient=B.ID_Patient AND B.ID_Depart=C.ID_Depart
Item_A=MAX(case when ItemName='Item_A' then ExamResult else null end),
Item_B=MAX(case when ItemName='Item_B' then ExamResult else null end),
Item_C=MAX(case when ItemName='Item_C' then ExamResult else null end),
Item_D=MAX(case when ItemName='Item_D' then ExamResult else null end),
Summary=MAX(Summary)
FROM #T_Patient A,#T_Depart B,#T_Item C
WHERE A.ID_Patient=B.ID_Patient AND B.ID_Depart=C.ID_Depart
GROUP BY PatientCode,PatientNamePatientCode PatientName Item_A Item_B Item_C Item_D Summary
----------- ----------- ------ ------ ------ ------ -------
20100001 张三 结果1 结果2 结果3 结果4 无异常
20100002 李四 结果5 结果6 结果7 结果8 异常1
20100003 王五 结果9 结果10 结果11 结果12 异常2
20100004 陈六 结果13 结果14 结果15 结果16 异常3
警告: 聚合或其他 SET 操作消除了空值。
if object_id('[T_Patient]') is not null drop table [T_Patient]
create table [T_Patient]([ID_Patient] varchar(6),[PatientCode] int,[PatientName] varchar(4))
insert [T_Patient]
select 'P_0001',20100001,'张三' union all
select 'P_0002',20100002,'李四' union all
select 'P_0003',20100003,'王五' union all
select 'P_0004',20100004,'陈六'
--> 测试数据:[T_Depart]
if object_id('[T_Depart]') is not null drop table [T_Depart]
create table [T_Depart]([ID_Depart] varchar(6),[DepartCode] varchar(8),[ID_Patient] varchar(6),[Summary] varchar(6))
insert [T_Depart]
select 'D_0001','Depart01','P_0001','无异常' union all
select 'D_0002','Depart01','P_0002','异常1' union all
select 'D_0003','Depart01','P_0003','异常2' union all
select 'D_0004','Depart01','P_0004','异常3'
--> 测试数据:[T_Item]
if object_id('[T_Item]') is not null drop table [T_Item]
create table [T_Item]([ID_Item] varchar(6),[ID_Depart] varchar(6),[ItemName] varchar(6),[ExamResult] varchar(6))
insert [T_Item]
select 'I_0001','D_0001','Item_A','结果1' union all
select 'I_0002','D_0001','Item_B','结果2' union all
select 'I_0003','D_0001','Item_C','结果3' union all
select 'I_0004','D_0001','Item_D','结果4' union all
select 'I_0005','D_0002','Item_A','结果5' union all
select 'I_0006','D_0002','Item_B','结果6' union all
select 'I_0007','D_0002','Item_C','结果7' union all
select 'I_0008','D_0002','Item_D','结果8' union all
select 'I_0009','D_0003','Item_A','结果9' union all
select 'I_0010','D_0003','Item_B','结果10' union all
select 'I_0011','D_0003','Item_C','结果11' union all
select 'I_0012','D_0003','Item_D','结果12' union all
select 'I_0013','D_0004','Item_A','结果13' union all
select 'I_0014','D_0004','Item_B','结果14' union all
select 'I_0015','D_0004','Item_C','结果15' union all
select 'I_0016','D_0004','Item_D','结果16'select * from [T_Patient]
select * from [T_Depart]
select * from [T_Item]/*
-PatientCode PatientName Item_A Item_B Item_C Item_D Summary
20100001 张三 结果1 结果2 结果3 结果4 无异常
20100002 李四 结果5 结果6 结果7 结果8 异常1
20100003 王五 结果9 结果10 结果11 结果12 异常2
20100004 陈六 结果13 结果14 结果15 结果16 异常3
*/
select A.patientcode,
A.patientname,
[Item_A] = MAX(CASE WHEN C.[ItemName]='Item_A' THEN C.[ExamResult] ELSE '' END) ,
Item_B = MAX(CASE WHEN C.[ItemName]='Item_B' THEN C.[ExamResult] ELSE '' END) ,
Item_C = MAX(CASE WHEN C.[ItemName]='Item_C' THEN C.[ExamResult] ELSE '' END) ,
Item_D = MAX(CASE WHEN C.[ItemName]='Item_D' THEN C.[ExamResult] ELSE '' END) ,
B.Summary
from [T_Patient] A
LEFT JOIN [T_Depart] B ON A.[ID_Patient] = B.[ID_Patient]
LEFT JOIN [T_Item] C ON B.[ID_Depart] = C.[ID_Depart]
GROUP BY A.patientcode, A.patientname,B.Summary
/*
patientcode patientname Item_A Item_B Item_C Item_D Summary
----------- ----------- ------ ------ ------ ------ -------
20100001 张三 结果1 结果2 结果3 结果4 无异常
20100002 李四 结果5 结果6 结果7 结果8 异常1
20100003 王五 结果9 结果10 结果11 结果12 异常2
20100004 陈六 结果13 结果14 结果15 结果16 异常3(4 行受影响)*/