第一种方法：
复制代码 代码如下:
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)