写的一个SQL的piovt的小例子

-----查询条件
declare @startDate varchar(20)--开始时间
declare @endDate   varchar(20)--结束时间
declare @brandId varchar(10) --项目ID
declare @ProductLevel varchar(5)--产品级数
declare @TopicLevel varchar(5)--话题级数
set @startDate='2012-7-18'
set @endDate='2012-7-20'
set @brandId='20003'
set @ProductLevel=1
set @TopicLevel=12
-----内部临时变量
declare @firstProducts varchar(max)
declare @secondProducts varchar(max)
declare @firstTopics varchar(max)
declare @secondTopics varchar(max)
declare @sqlPageTable varchar(max)
declare @sqlFirstProductTable varchar(max)
declare @sqlSecondProductTable varchar(max)
declare @sqlFirstTopicTable varchar(max)
declare @sqlSecondTopicTable varchar(max)
declare @TableCondition varchar(100)
declare @RelevanceCondition varchar(max)
declare @WhereCondition varchar(500)
declare @SQL varchar(max)

-----获得某品牌下的所有产品
select @firstProducts=ISNULL(@firstProducts+',','')+'['+ProductName+']' FROM [IWT_BrandProduct] where [ParentProductID]=0 and BrandID=@BrandID
select @secondProducts=ISNULL(@secondProducts+',','')+'['+ProductName+']' FROM [IWT_BrandProduct] where [ParentProductID]<>0 and BrandID=@BrandID
-----获得某品牌下的所有话题
select @firstTopics=ISNULL(@firstTopics+',','')+'['+TagSearchName+']' FROM (select distinct TagSearchName from [IWT_BrandTag] where ParentTagID=0 and BrandID=@BrandID) as A
select @secondTopics=ISNULL(@secondTopics+',','')+'['+TagSearchName+']' FROM (select distinct TagSearchName from [IWT_BrandTag] where ParentTagID<>0 and BrandID=@BrandID) as A
-----获得出现的关联表
set @TableCondition= case @ProductLevel when 1 then ' ,FirstProductTable.* ' when 2 then ' ,SecondProductTable.* ' when 12 then ',FirstProductTable.*  ,SecondProductTable.* ' end 
set @TableCondition= case @TopicLevel when 1 then @TableCondition+' ,FirstTopicTable.* ' when 2 then @TableCondition+' ,SecondTopicTable.* ' when 12 then @TableCondition+' ,FirstTopicTable.*  ,SecondTopicTable.* ' end 
---------------------------------------------------------------------------page的基本信息
set @sqlPageTable=
'SELECT      [TempPageViewTest].Page_ID
            ,Page_Title as 标题
            ,Page_URL as 网址
            ,case ISNULL(Page_ProState,-1) when -1 then ''未处理'' when 0 then ''未处理'' when 1 then ''已处理'' when 2 then ''已关注'' when 3 then ''需建议'' end AS 处理状态
            ,case ISNULL(Page_AuditState,-1) when -1 then ''未审核'' when 0 then ''未审核'' when 1 then ''已审核'' end as 审核状态
            ,Isnull(Page_CustomerState,0) as 是否建议
            ,Isnull(Page_Storey,0) as 楼层数
            ,Isnull(Page_Extend,0) as 是否正面
            ,case Page_IsArtificial when 0 then ''系统抓取'' when 1 then ''人工录入'' end as 分拣类型
            ,convert(varchar(8),Page_LastReplyTime,112) 最后回复日期
            ,convert(varchar(8),Page_UDate,112) 修改日期
            ,convert(varchar(8),Page_CDate,112) 分拣日期
            ,convert(varchar(8),Page_PostTime,112) 发布日期
            ,Page_ReplyCount as 回复数
            ,Page_VisitCount as 访问数
            ,PageDomain_NetName as 媒体
            ,siteTypeNew_title as 媒体类别
            ,case Page_Attitude when 1 then ''攻击'' when 2 then ''负面'' when 3 then ''中立'' when 4 then ''正面'' when 5 then ''赞扬'' end as 健康度
            ,Page_Return
            ,Admin_RealName as 分拣员
            ,case ISNULL(Treadate,1) when 1 then '''' end  as 处理时间
            ,case Page_FromWeb when '''' then 0 else 1 end 是否转载
            ,Page_area as 地区
            '+@TableCondition+'
            FROM [dbo].[TempPageViewTest]'
---------------------------------------------------------------------------一级产品部分
set @sqlFirstProductTable=
'SELECT  * FROM
               (SELECT DISTINCT      
                      dbo.IWT_Page.Page_ID                   
                     ,IWT_BrandProduct_1.ProductName
                     ,1 AS TempC                 
                FROM  dbo.IWT_BrandTag INNER JOIN
                      dbo.IWT_PageTag ON dbo.IWT_BrandTag.TagID = dbo.IWT_PageTag.TagID INNER JOIN
                      dbo.IWT_Page ON dbo.IWT_PageTag.PageID = dbo.IWT_Page.Page_ID INNER JOIN
                      dbo.IWT_BrandProduct ON dbo.IWT_BrandTag.ProductID = dbo.IWT_BrandProduct.ProductID INNER JOIN
                      dbo.IWT_BrandProduct AS IWT_BrandProduct_1 ON dbo.IWT_BrandProduct.ParentProductID=IWT_BrandProduct_1.ProductID
                WHERE dbo.IWT_Page.Brand_ID='+@brandId+') as A
                      pivot
                      (
                       COUNT(A.Tempc) FOR A.ProductName IN('+@firstProducts+')
                )as P'
set @sqlFirstProductTable=' LEFT JOIN ('+@sqlFirstProductTable+') as FirstProductTable ON [TempPageViewTest].Page_ID=FirstProductTable.Page_ID '
---------------------------------------------------------------------------二级产品部分
set @sqlSecondProductTable=
'SELECT  * FROM
               (SELECT DISTINCT      
                      dbo.IWT_Page.Page_ID                   
                     ,dbo.IWT_BrandProduct.ProductName
                     ,1 AS TempC                 
                FROM  dbo.IWT_BrandTag INNER JOIN
                      dbo.IWT_PageTag ON dbo.IWT_BrandTag.TagID = dbo.IWT_PageTag.TagID INNER JOIN
                      dbo.IWT_Page ON dbo.IWT_PageTag.PageID = dbo.IWT_Page.Page_ID INNER JOIN
                      dbo.IWT_BrandProduct ON dbo.IWT_BrandTag.ProductID = dbo.IWT_BrandProduct.ProductID INNER JOIN
                      dbo.IWT_BrandTag AS IWT_BrandTag_1 ON dbo.IWT_BrandTag.ParentTagID = IWT_BrandTag_1.TagID
                WHERE dbo.IWT_Page.Brand_ID='+@brandId+') as A
                      pivot
                      (
                       COUNT(A.Tempc) FOR A.ProductName IN('+@secondProducts+')
                )as P' 
set @sqlSecondProductTable= ' LEFT JOIN ('+@sqlSecondProductTable+') as SecondProductTable ON [TempPageViewTest].Page_ID=SecondProductTable.Page_ID '             
---------------------------------------------------------------------------一级话题部分
set @sqlFirstTopicTable=
'SELECT  * FROM
                (SELECT DISTINCT     
                      dbo.IWT_Page.Page_ID                   
                     ,IWT_BrandTag_1.TagName AS fristTag 
                     ,1 AS TempC                 
                 FROM dbo.IWT_BrandTag 
                      INNER JOIN dbo.IWT_PageTag ON dbo.IWT_BrandTag.TagID = dbo.IWT_PageTag.TagID 
                      INNER JOIN dbo.IWT_Page ON dbo.IWT_PageTag.PageID = dbo.IWT_Page.Page_ID 
                      INNER JOIN dbo.IWT_BrandTag AS IWT_BrandTag_1 ON dbo.IWT_BrandTag.ParentTagID = IWT_BrandTag_1.TagID
                 WHERE dbo.IWT_Page.Brand_ID='+@brandId+') as A
                       pivot
                       (
                        COUNT(A.Tempc) FOR A.fristTag IN('+@firstTopics+')
                       )as T '   
set @sqlFirstTopicTable=' LEFT JOIN ('+@sqlFirstTopicTable+') as FirstTopicTable ON [TempPageViewTest].Page_ID=FirstTopicTable.Page_ID '
---------------------------------------------------------------------------二级话题部分
set @sqlSecondTopicTable=
'SELECT  * FROM
                (SELECT DISTINCT     
                      dbo.IWT_Page.Page_ID                   
                     ,dbo.IWT_BrandTag.TagName AS secondTag 
                     ,1 AS TempC                 
                 FROM dbo.IWT_BrandTag 
                      INNER JOIN dbo.IWT_PageTag ON dbo.IWT_BrandTag.TagID = dbo.IWT_PageTag.TagID 
                      INNER JOIN dbo.IWT_Page ON dbo.IWT_PageTag.PageID = dbo.IWT_Page.Page_ID 
                      INNER JOIN dbo.IWT_BrandTag AS IWT_BrandTag_1 ON dbo.IWT_BrandTag.ParentTagID = IWT_BrandTag_1.TagID
                 WHERE dbo.IWT_Page.Brand_ID='+@brandId+') as A
                       pivot
                       (
                        COUNT(A.Tempc) FOR A.secondTag IN('+@secondTopics+')
                       )as T '
set  @sqlSecondTopicTable= ' LEFT JOIN ('+@sqlSecondTopicTable+') as SecondTopicTable ON [TempPageViewTest].Page_ID=SecondTopicTable.Page_ID ' 
---------------------------------------------------------------------------获得关联条件
set @RelevanceCondition=case @ProductLevel when 1  then @sqlFirstProductTable
                                           when 2  then @sqlSecondProductTable
                                           when 12 then @sqlFirstProductTable+@sqlSecondProductTable
                         end 
set @RelevanceCondition=case @TopicLevel   when 1  then @RelevanceCondition + @sqlFirstTopicTable
                                           when 2  then @RelevanceCondition + @sqlSecondTopicTable
                                           when 12 then @RelevanceCondition + @sqlFirstTopicTable + @sqlSecondTopicTable
                         end 
set @WhereCondition=' WHERE Brand_ID='+@brandId+' and Page_PostTime >='''+@startDate+''' and Page_PostTime<'''+@endDate+''''                                        
---------------------------------------------------------------------------拼接查询的SQL语句
set @SQL=@sqlPageTable+@RelevanceCondition+@WhereCondition
--print @SQL
exec(@SQL)