行专列+统计+时间查询条件,在线等!请各位高手赐教,不胜感激!!!!

我是一只小小小的菜鸟 2008-03-17 07:39:35


原始表格如下:

InTime DXUnit GrossWeight
2007-1-20 A 2000
2007-2-21 B 300
2007-3-20 C 400
2007-4-20 B 300
2008-1-20 A 200
2008-2-20 A 100
2008-3-20 B 300
2008-4-20 C 200
我想要的结果是这样的!
在排列之后,外面有个时间条件,如果我修选择的时间是2007
则现实如下
Intime A B C 总计
2007-01 2000 0 0 2000
2007-02 0 300 0 300
2007-03 0 0 400 400
2007-04 0 300 0 300
2007 2000 600 400 3000

如果我时间选择的是2008的话,哪么现实也和上面的一样的行列统计!



...全文
28 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
也谢谢这位大哥!
虽然没有达到俺的要求,但是俺还是要谢谢你帮助俺!也是位高手!
回复
大哥!
啥也不说了!
就一个字!
牛!!!
回复
pt1314917 2008-03-17

create table tb(InTime datetime,DXUnit varchar(20),GrossWeight float)
insert into tb select '2007-1-20','A','2000'
insert into tb select '2007-2-21','B','300'
insert into tb select '2007-3-20','C','400'
insert into tb select '2007-4-20','B','300'
insert into tb select '2008-1-20','A','200'
insert into tb select '2008-2-20','A','100'
insert into tb select '2008-3-20','B','300'
insert into tb select '2008-4-20','C','200'
go

declare @year varchar(4)
set @year='2007'
declare @sql varchar(8000),@sql1 varchar(8000)
set @sql='select convert(varchar(7),intime,120)[Intime]'
select @sql=@sql+','+DXunit+'=sum(case DXunit when '''+DXunit+''' then grossweight else 0 end)'
from (select distinct DXunit from tb where datepart(yy,intime)=@year)a
set @sql=@sql+',sum(grossweight)[总计] from tb where datepart(yy,intime)='''+@year+''' group by convert(varchar(7),intime,120)'
set @sql1='select datename(yy,intime)'
select @sql1=@sql1+','+DXunit+'=sum(case DXunit when '''+DXunit+''' then grossweight else 0 end)'
from (select distinct DXunit from tb where datepart(yy,intime)=@year)a
set @sql1=@sql1+',sum(grossweight)[总计] from tb where datepart(yy,intime)='''+@year+''' group by datename(yy,intime)'
exec(@sql+ ' union all '+@sql1)

回复
jinjazz 2008-03-17
--建立测试环境
set nocount on
create table test(InTime datetime,DXUnit varchar(20),GrossWeight float)
insert into test select '2007-1-20','A','2000'
insert into test select '2007-2-21','B','300'
insert into test select '2007-3-20','C','400'
insert into test select '2007-4-20','B','300'
insert into test select '2008-1-20','A','200'
insert into test select '2008-2-20','A','100'
insert into test select '2008-3-20','B','300'
insert into test select '2008-4-20','C','200'
go
--测试

declare @time varchar(4)
set @time='2007'
declare @sql varchar(8000)
set @sql='select intime,'
select @sql=@sql+'sum(case when dxunit='''+dxunit+''' then GrossWeight else 0 end)['
+dxunit+'],' from (select distinct dxunit from test) a
set @sql=@sql+'sum(GrossWeight)[all] from test where year(intime)='+@time+' group by intime'
print @sql
exec (@sql)





--删除测试环境
drop table test
set nocount off
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-17 07:39
社区公告
暂无公告