sql动态行转列的两种方法

第一种方法：

select *from ( select url,case  when  month=01 then  '1月' when  month=02 then '2月' when  month=03 then  '3月' when  month=04 then '4月' when  month=05 then  '5月' when  month=06 then '6月' when  month=07 then  '7月' when  month=08 then '8月' when  month=09 then  '9月' when  month=10 then ' 10月' when  month=11 then  '11月' when  month=12 then ' 12月'

 end  month,quality from  (

select url,datename(m,auditingtime)month,sum(quality) quality from  tb_order as a left join  tb_websiteinfo as b on a.websiteinfoid=b.id left join  tb_orderlist as c on c.orderid=a.id where auditingtime>'2013-01-01' and b.id>0 and auditing=2

group by url,datename(m,auditingtime) )as h ) as hh

 pivot ( sum(quality) for month in([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) as a

第二种方法：

declare @sql varchar(8000)

select @sql = isnull(@sql + ',' , '') + '['+convert(varchar(7),auditingtime,20)+']'

from  tb_order as a left join  tb_websiteinfo as b on a.websiteinfoid=b.id left join  tb_orderlist as c on c.orderid=a.id where auditingtime>'2013-01-01' and b.id>0 and auditing=2

group by convert(varchar(7),auditingtime,20) print @sql declare @sql2 varchar(8000)='' set @sql2=' select *from (

select url, convert(varchar(7),auditingtime,20) auditingtime,sum(quality) quality from  tb_order as a left join  tb_websiteinfo as b on a.websiteinfoid=b.id left join  tb_orderlist as c on c.orderid=a.id where b.id>0 and auditing=2

group by url, convert(varchar(7),auditingtime,20)

) as hh pivot (sum(quality) for auditingtime in (' + @sql + ')) b'

print @sql2

exec(@sql2)