sql 实现表的行列转换

 下面的两个存储过程是公司网站统计编辑发布哪些类型的文章用到的，实现了数据表的行列转换

create proc EditorWork_AccountByTopicname1--编辑工作按文章内容类型统计各栏目的文章数
@starttime varchar(50),--查询开始时间
@endtime varchar(50)--查询结束时间
as

declare @sql varchar(8000)
set @sql='select content_topic_name '
select @sql=@sql+',['+ctname+']=sum(case ctname when '''+ctname+''' then counts else 0 end)' from (select count(a.id) as counts,ctname=isnull(case a.InfoContentType when 1 then '入门' when 2 then '技术' when 3 then '市场' when 4 then '理论研究' when 5 then '产品分析' when 6 then '应用实施' when 7 then '典型案例' end,'ZERO'),b.content_topic_name  from articleinfo a,content_topic b where b.content_topic_id=a.contenttopic and a.contenttopic<>0 and a.addtime<@endtime and a.addtime>@starttime group by InfoContentType,content_topic_name) aa
group by ctname
print @sql
exec(@sql+' from (select count(a.id) as counts,ctname=isnull(case a.InfoContentType when 1 then ''入门'' when 2 then ''技术'' when 3 then ''市场'' when 4 then ''理论研究'' when 5 then ''产品分析'' when 6 then ''应用实施'' when 7 then ''典型案例'' end,''ZERO''),b.content_topic_name from articleinfo a,content_topic b where b.content_topic_id=a.contenttopic and a.contenttopic<>0 and a.addtime<'''+@endtime+''' and a.addtime>'''+@starttime+''' group by InfoContentType,content_topic_name ) aa group by content_topic_name')

 

GO