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