在做报表的时候经常用到行列转换。下面贴上我做报表时用的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
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