--要是只有2004年一年的数据的话,写死算了.
select
case when month(balanceDate)=1 then costMoney end as '1月',
case when month(balanceDate)=2 then costMoney end as '2月',
case when month(balanceDate)=3 then costMoney end as '3月',
case when month(balanceDate)=4 then costMoney end as '4月',
case when month(balanceDate)=5 then costMoney end as '5月',
case when month(balanceDate)=6 then costMoney end as '6月',
case when month(balanceDate)=7 then costMoney end as '7月',
case when month(balanceDate)=8 then costMoney end as '8月',
case when month(balanceDate)=9 then costMoney end as '9月',
case when month(balanceDate)=10 then costMoney end as '10月',
case when month(balanceDate)=11 then costMoney end as '11月',
case when month(balanceDate)=12 then costMoney end as '12月'
from costTable
select
case when month(balanceDate)=1 then costMoney end as '1月',
case when month(balanceDate)=2 then costMoney end as '2月',
case when month(balanceDate)=3 then costMoney end as '3月',
case when month(balanceDate)=4 then costMoney end as '4月',
case when month(balanceDate)=5 then costMoney end as '5月',
case when month(balanceDate)=6 then costMoney end as '6月',
case when month(balanceDate)=7 then costMoney end as '7月',
case when month(balanceDate)=8 then costMoney end as '8月',
case when month(balanceDate)=9 then costMoney end as '9月',
case when month(balanceDate)=10 then costMoney end as '10月',
case when month(balanceDate)=11 then costMoney end as '11月',
case when month(balanceDate)=12 then costMoney end as '12月'
from costTable
解决方案 »
- 求一个行列转换SQL
- sql 字段“将截断字符串或二进制数据的问题” 直接给分
- 怎么把按照某个列的sum值算出来
- 来看看这个SQL语句怎么写:如果数据库有记录,则更新记录;如果无记录,则插入记录?
- 多级别多对多关系如何建表
- SCOM2007中如何修改报表的模板
- 自定义函数问题!! 急啊,帮忙看看
- 求助:一条sql语句的写法
- 各位好,有个关于数据库MDF文件附加的问题请教!
- 将access数据库转换成sql后用sqloledb重新建立连接后提示:Microsoft OLE DB Provider for SQL Server 错误 '80004005' 用户 'sa' 登录失
- 怎么写这个SQL语句
- 多表查询的问题
select
sum(isnull(case when month(balanceDate)=1 then costMoney end,0)) as '1月',
sum(isnull(case when month(balanceDate)=2 then costMoney end,0)) as '2月',
sum(isnull(case when month(balanceDate)=3 then costMoney end,0)) as '3月',
sum(isnull(case when month(balanceDate)=4 then costMoney end,0)) as '4月',
sum(isnull(case when month(balanceDate)=5 then costMoney end,0)) as '5月',
sum(isnull(case when month(balanceDate)=6 then costMoney end,0)) as '6月',
sum(isnull(case when month(balanceDate)=7 then costMoney end,0)) as '7月',
sum(isnull(case when month(balanceDate)=8 then costMoney end,0)) as '8月',
sum(isnull(case when month(balanceDate)=9 then costMoney end,0)) as '9月',
sum(isnull(case when month(balanceDate)=10 then costMoney end,0)) as '10月',
sum(isnull(case when month(balanceDate)=11 then costMoney end,0)) as '11月',
sum(isnull(case when month(balanceDate)=12 then costMoney end,0)) as '12月'
from costTable
CREATE TABLE [costTable] (
[costMoney] [money] NULL ,
[balanceDate] [datetime] NULL
) ON [PRIMARY]
GO
insert into costTable
select 100.0000 , '2004-01-01 00:00:00.000' union all
select 100.0000 , '2004-02-01 00:00:00.000' union all
select 100.0000 , '2004-03-01 00:00:00.000' union all
select 100.0000 , '2004-04-01 00:00:00.000' union all
select 100.0000 , '2004-05-01 00:00:00.000' union all
select 100.0000 , '2004-06-01 00:00:00.000' union all
select 100.0000 , '2004-07-01 00:00:00.000' union all
select 100.0000 , '2004-08-01 00:00:00.000' union all
select 100.0000 , '2004-09-01 00:00:00.000' union all
select 100.0000 , '2004-10-01 00:00:00.000' union all
select 100.0000 , '2004-11-01 00:00:00.000' union all
select 100.0000 , '2004-12-01 00:00:00.000'
select
sum(isnull(case when month(balanceDate)=1 then costMoney end,0)) as '1月',
sum(isnull(case when month(balanceDate)=2 then costMoney end,0)) as '2月',
sum(isnull(case when month(balanceDate)=3 then costMoney end,0)) as '3月',
sum(isnull(case when month(balanceDate)=4 then costMoney end,0)) as '4月',
sum(isnull(case when month(balanceDate)=5 then costMoney end,0)) as '5月',
sum(isnull(case when month(balanceDate)=6 then costMoney end,0)) as '6月',
sum(isnull(case when month(balanceDate)=7 then costMoney end,0)) as '7月',
sum(isnull(case when month(balanceDate)=8 then costMoney end,0)) as '8月',
sum(isnull(case when month(balanceDate)=9 then costMoney end,0)) as '9月',
sum(isnull(case when month(balanceDate)=10 then costMoney end,0)) as '10月',
sum(isnull(case when month(balanceDate)=11 then costMoney end,0)) as '11月',
sum(isnull(case when month(balanceDate)=12 then costMoney end,0)) as '12月'
from costTable------------------------------------------------------------------------------
1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
100 100 100 100 100 100 100 100 100 100 100 100(所影响的行数为 1 行)
if exists (select * from sysobjects where id = object_id('proc_sky_blue') and xtype ='P')
drop proc proc_sky_blue
go
create proc proc_sky_blue (@tablename varchar(200))
as
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了,我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
end
endgo
----------------分析
declare @tablename varchar(200)
set @tablename='table1'
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了,我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
select @insertsql
--exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
drop table #tmp
end
end
declare @sql varchar(8000)
set @sql = ''
select @sql =@sql+ ',isnull(sum(case when month(balanceDate)= '''+cast(balanceDate as varchar(10))+''' then costMoney end),0) ['+cast(balanceDate as varchar(10)) +'月]'
from (select distinct month(balanceDate) as balanceDate from costTable ) a
set @sql='select '+stuff(@sql,1,1,'')
select @sql = @sql+' from costTable '
exec (@sql)