SQL结果集横向显示问题
表1
vendor_id,venodr_name
0001,科伦医贸
表2
vendor_id,cer_yxq,cer_id
0001,2015-01-01,01
0001,2016-03-01,02
0001,2017-09-08,03
0001,2013-08-01,04
表3
cer_id,cer_name
01,AAAA
02,BBBB
03,CCCC
04,DDDD
现在需要将结要集横向显示出来,且当cer_yxq小于等于当时间时显示不合格,大于时显示合格,请问此语句该如何写?
要求结果如下
venodr_id,vendor_name,cer_name,合格否,cer_name,合格否,cer_name,合格否........... 展开
--SQL2005或以上版本(格式1)
;
With T
As
(
Select A.vendor_id,A.venodr_name,C.cer_name,Case When cer_yxq>getdate() Then '合格' Else '不合格' End isHG From 表1 A
Left Join 表2 B on A.vendor_id=B.vendor_id
Left Join 表3 C on B.cer_id=C.cer_id
)
Select vendor_id,venodr_name,
(Select cer_name+','+isHG+' ' From T
Where vendor_id=A.vendor_id
for xml Path('')
) As Rst
From T A
Group by vendor_id,venodr_name
--SQL2005或以上版本,静态行列转换(格式2)
;
With T
AS
(
Select A.vendor_id,A.venodr_name,C.cer_name,Case When cer_yxq>getdate() Then '合格' Else '不合格' End isHG From 表1 A
Left Join 表2 B on A.vendor_id=B.vendor_id
Left Join 表3 C on B.cer_id=C.cer_id
)
Select vendor_id,venodr_name,[AAAA],[BBBB],[CCCC],[DDDD] From
(
Select vendor_id,venodr_name,cer_name,isHG From T
) p
PIVOT
(
max(isHG)
for cer_name in ([AAAA],[BBBB],[CCCC],[DDDD])
) as pst
--通用,动态版本(格式2)
Declare @sql Varchar(8000)
Set @sql='Select vendor_id,venodr_name '
Select @sql=@sql + ' , Max(Case cer_name When ''' + cer_name + ''' Then isHG Else Null End) [' +cer_name+']'
From (Select Distinct cer_name From 表3 ) As a
Set @sql=@sql + ' From
(
Select A.vendor_id,A.venodr_name,C.cer_name,Case When cer_yxq>getdate() Then ''合格'' Else ''不合格'' End isHG From 表1 A
Left Join 表2 B on A.vendor_id=B.vendor_id
Left Join 表3 C on B.cer_id=C.cer_id
) A Group By vendor_id,venodr_name'
exec(@sql)
declare
CURSOR c_vendor IS
select v.vendor_id,v.vendor_name,d.cer_yxq,c.cer_name from vendor v,vdate d,vcer c
where v.vendor_id = d.vendor_id(+)
and d.cer_id = c.cer_id(+)
order by v.vendor_name;
v_print varchar2(200);
v_cer varchar2(20);
v_vendor_name varchar2(200);
v_vendor_id number;
begin
for rec_vendor in c_vendor loop
v_cer :=
case
when to_date(rec_vendor.cer_yxq)<sysdate Then 'NO'
when rec_vendor.cer_yxq IS NULL THEN 'not exit'
ELSE 'Yes'
end;
if v_vendor_id = rec_vendor.vendor_id then
v_print :=v_print||' '||rec_vendor.cer_name||' '||v_cer;
else
dbms_output.put_line(v_vendor_name||' '||v_vendor_id||' '||v_print);
v_vendor_name := rec_vendor.vendor_name;
v_vendor_id := rec_vendor.vendor_id;
v_print :=rec_vendor.cer_name||' '||v_cer;
end if;
END LOOP;
dbms_output.put_line(v_vendor_name||' '||v_vendor_id||' '||v_print);
END;
____________________________________________________
以上代码均经过测试，可以实现要求。 环境 oracle 10g/toad
——————————————————————————————
输出结果
