展开全部
--SQL2005或以上版本32313133353236313431303231363533e78988e69d8331333335313830(格式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)