求教全年分月交叉汇总表（要求动态sql）。

[老七]

数据结构：
--create table biao1(name varchar(10),date datetime,money decimal(10,2))
insert into biao1 select '张三','2005-12-01',111.11
union all select '李四','2005-12-02',111.11
要求结果：
姓名 01月 02月 03月 04月 05月 06月 07月 08月 09月 10月 11月 12月 总计
张三 222.22 222.22
李四 222.22 222.22
总计 444.44 444.44

谢谢

[老七]

数字对应12月和总计列下面。

[paoluo]

Declare @I Int
Declare @S Nvarchar(4000)
Select @S='',@I=1
While @I<=12
Begin
Select @S=@S+', Max(Case Month([date]) When '+Rtrim(@I)+' Then money Else 0 End) As ['+Right(100+@I,2)+N'月]'
Select @I=@I+1
End
Select @S='Select name'+@S+N',SUM([money]) As 总计 From biao1 Group By name Union All Select N''总计'''+@S+',SUM([money]) From biao1'
Select @S
EXEC(@S)

[paoluo]

create table biao1(name Nvarchar(10),date datetime,money decimal(10,2))
insert into biao1 select N'张三','2005-12-01',111.11
union all select N'李四','2005-12-02',111.11
GO
Declare @I Int
Declare @S Nvarchar(4000)
Select @S='',@I=1
While @I<=12
Begin
Select @S=@S+', Max(Case Month([date]) When '+Rtrim(@I)+' Then money Else 0 End) As ['+Right(100+@I,2)+N'月]'
Select @I=@I+1
End
Select @S='Select name'+@S+N',SUM([money]) As 总计 From biao1 Group By name Union All Select N''总计'''+@S+',SUM([money]) From biao1'
--Select @S
EXEC(@S)
GO
Drop Table biao1
--Result
/*
name 01月 02月 03月 04月 05月 06月 07月 08月 09月 10月 11月 12月 总计
李四 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 111.11 111.11
张三 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 111.11 111.11
总计 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 111.11 222.22
*/

[老七]

加了一个数据，元月数据不对。
create table biao1(name Nvarchar(10),date datetime,money decimal(10,2))
insert into biao1 select N'张三','2005-01-01',111.11
union all select N'张三','2005-01-02',111.11
union all select N'李四','2005-12-02',111.11
GO
Declare @I Int
Declare @S Nvarchar(4000)
Select @S='',@I=1
While @I<=12
Begin
Select @S=@S+', Max(Case Month([date]) When '+Rtrim(@I)+' Then money Else 0 End) As ['+Right(100+@I,2)+N'月]'
Select @I=@I+1
End
Select @S='Select name'+@S+N',SUM([money]) As 总计 From biao1 Group By name Union All Select N''总计'''+@S+',SUM([money]) From biao1'
--Select @S
EXEC(@S)
GO
Drop Table biao1

[paoluo]

改為存儲過程，再加上年份做判斷。


Create ProceDure SP_Report(@Year Int)
As
Begin
Declare @I Int
Declare @S Nvarchar(4000)
Select @S='',@I=1
While @I<=12
Begin
Select @S=@S+', Max(Case Month([date]) When '+Rtrim(@I)+' Then money Else 0 End) As ['+Right(100+@I,2)+N'月]'
Select @I=@I+1
End
Select @S='Select name'+@S+N',SUM([money]) As 总计 From biao1 Where Year([date])='+Rtrim(@Year)+N' Group By name Union All Select N''总计'''+@S+',SUM([money]) From biao1 Where Year([date])='+Rtrim(@Year)
EXEC(@S)
End
GO
EXEC SP_Report 2005

[paoluo]

加了一个数据，元月数据不对。
------------
不可能啊，我這裡都OK的，你再看看。

[老七]

你就运行我贴的内容。

[paoluo]

發現問題了，以為你一個月只有一條數據，不好意思，改下



create table biao1(name Nvarchar(10),date datetime,money decimal(10,2))
insert into biao1 select N'张三','2005-01-01',111.11
union all select N'张三','2005-01-02',111.11
union all select N'李四','2005-12-02',111.11
GO
Declare @I Int
Declare @S Nvarchar(4000)
Select @S='',@I=1
While @I<=12
Begin
Select @S=@S+', SUM(Case Month([date]) When '+Rtrim(@I)+' Then money Else 0 End) As ['+Right(100+@I,2)+N'月]'
Select @I=@I+1
End
Select @S='Select name'+@S+N',SUM([money]) As 总计 From biao1 Group By name Union All Select N''总计'''+@S+',SUM([money]) From biao1'
--Select @S
EXEC(@S)
GO
Drop Table biao1

[fcuandy]

这种分不蹭..

[paoluo]

存儲過程改為


Create ProceDure SP_Report(@Year Int)
As
Begin
Declare @I Int
Declare @S Nvarchar(4000)
Select @S='',@I=1
While @I<=12
Begin
Select @S=@S+', SUM(Case Month([date]) When '+Rtrim(@I)+' Then money Else 0 End) As ['+Right(100+@I,2)+N'月]'
Select @I=@I+1
End
Select @S='Select name'+@S+N',SUM([money]) As 总计 From biao1 Where Year([date])='+Rtrim(@Year)+N' Group By name Union All Select N''总计'''+@S+',SUM([money]) From biao1 Where Year([date])='+Rtrim(@Year)
EXEC(@S)
End
GO
EXEC SP_Report 2005

[老七]

已通过。请问存储过程的作用。

[paoluo]

你用動態語句，肯定是要寫成存儲過程的。

寫成存儲過程後，你執行起來就簡單了，只需一句代碼：EXEC SP_Report 2005。

[paoluo]

汗一下，可以改寫下。


create table biao1(name Nvarchar(10),date datetime,money decimal(10,2))
insert into biao1 select N'张三','2005-01-01',111.11
union all select N'张三','2005-01-02',111.11
union all select N'李四','2005-12-02',111.11
GO
Declare @I Int
Declare @S Nvarchar(4000)
Select @S='',@I=1
While @I<=12
Begin
Select @S=@S+', SUM(Case Month([date]) When '+Rtrim(@I)+' Then money Else 0 End) As ['+Right(100+@I,2)+N'月]'
Select @I=@I+1
End
Select @S=N'Select IsNull(name,N''总计'') As name '+@S+N',SUM([money]) As 总计 From biao1 Group By name With Rollup'
--Select @S
EXEC(@S)
GO
Drop Table biao1