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)
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)
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
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
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
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
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