mysql 结果集横向显示_SQL结果集横向显示问题

展开全部

--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)