行列转换实例

在做报表的时候经常用到行列转换。下面贴上我做报表时用的sql代码。


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--===============================
--Function:Procedure
--            客服來電記錄統計報表
--Create Person:huyang
--Create Date:2008/05/05
--Para:@whereExp 查詢條件
--       @Index 來電類別
--Example:exec [usp_ReportCSCall] 'where ReceiveCallDate=''2008-05-05''',1
--===============================
ALTER Procedure [dbo].[usp_ReportCSCall]
                 @whereExp nvarchar(400),
                 @Index int

As
Begin
    if object_id(N'tempdb..##Information',N'U') IS NOT NULL
        BEGIN
            drop table ##Information
        END
    --來電信息
    declare @sql_Info nvarchar(1000)
    set @sql_Info=''
    set @sql_Info='select ItemParentNo=Case when Isnull(ItemParentNo,'''')='''' then ''10'' else ItemParentNo end ,ItemNo=Case when Isnull(ItemNo,'''')=''1001'' then '''' when Isnull(ItemNo,'''')=''1002'' then ''''   when ItemParentNo=''01'' and Isnull(ItemNo,'''')='''' then ''0109''  else Isnull(ItemNo,'''') end ,
    fTime=Case when Convert(Int,substring(ReceiveCallTime,0,charindex('':'',ReceiveCallTime,0)))<09  then Convert(Int,substring(ReceiveCallTime,0,charindex('':'',ReceiveCallTime,0)))+12 when Convert(Int,substring(ReceiveCallTime,0,charindex('':'',ReceiveCallTime,0)))>21 then 21 else
    Convert(Int,substring(ReceiveCallTime,0,charindex('':'',ReceiveCallTime,0))) end
    into ##Information
    from CS_ReceiveInformation ' + @whereExp
    print @sql_Info
    exec(@sql_Info)
    --報表模板
    select a.fCode,a.fName,b.fTime,b.fRemark,fAmount=0 into #template from CS_CusterServiceType
    a left join BSC_EVERYTIME b on 1=1   where Isnull(a.fType,0)=@Index  and a.fCode not in ('1001','1002','01')  order by fCode

    --統計來電信息
    select ItemParentNo,ItemNo,fTime,fAmount=Count(*) into #dd from ##Information
    group by ItemParentNo,ItemNo,fTime
    order by ItemParentNo,ItemNo,fTime

    --填充數據
    Update #template set fAmount=b.fAmount from #template a  inner join #dd b on a.fCode=Case when b.ItemNo='' then b.ItemParentNo else b.ItemNo end and a.fTime=b.fTime

    declare @sql varchar(8000)
    set @sql = 'select fCode,fName'
    select @sql = @sql + ' , max(case fRemark when ''' + fRemark + ''' then fAmount else 0 end) [' + fRemark + ']'
    from (select distinct fRemark from #template) as a
    set @sql = @sql + ' from #template group by fCode,fName order by fCode'
    print @sql

    exec(@sql)
    drop table ##Information
    drop table #template
    drop table #dd
   
End
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO