实例明细表如下
收发日期 物码及规格名称 单位 收发数量
2011-08-28 抗盐 吨 0.500
2011-08-29 抗盐 吨 0.200
2011-08-27 防塌 吨 3.000
2011-08-28 抗饱 吨 0.500
2011-08-29 抗饱 吨 1.0002011-08-30 抗饱 吨 5.000
编写sql 生成如下表物码及规格名称 单位 2011-08-27 2011-08-28 2011-08-29 2011-08-29 2011-08-30 抗盐 吨 0.5 0.2防塌 吨 3 0 1抗饱 吨 0.5 1 5
在线等待。谢谢
收发日期 物码及规格名称 单位 收发数量
2011-08-28 抗盐 吨 0.500
2011-08-29 抗盐 吨 0.200
2011-08-27 防塌 吨 3.000
2011-08-28 抗饱 吨 0.500
2011-08-29 抗饱 吨 1.0002011-08-30 抗饱 吨 5.000
编写sql 生成如下表物码及规格名称 单位 2011-08-27 2011-08-28 2011-08-29 2011-08-29 2011-08-30 抗盐 吨 0.5 0.2防塌 吨 3 0 1抗饱 吨 0.5 1 5
在线等待。谢谢
解决方案 »
- 请教各位大大,一个表的设计问题,小弟急用...
- 存储过程间调用?
- SQL Server Management Studio 2005 连接远程数据库问题
- 依赖性和扩展依赖有什么区别呀?
- sql使用 left outer join 排重问题!
- 高分相求,一个*.dat数据文件的导入问题:)
- 初学者的问题,关于SQL SERVER客户端,大家帮帮忙,在急用!!!
- 为什么我在sql控制台,对有些表不能删除?
- 如何在临时表中调用别的存储过程生成的数据集
- 一个有点难的查询排序问题!求高手尽快决解!谢谢!
- 如何新建链接到EXCEL2007表的链接服务器?
- 如果把业务逻辑的存储过程放在数据库,利弊~请帮忙分析下。
set @sql = 'select 物码及规格名称, 单位'
select @sql = @sql + ' , max(case convert(varchar(10),收发日期,120) when '''
+convert(varchar(10),收发日期,120)+ ''' then 收发数量 end) [' + convert(varchar(10),收发日期,120) + ']'
from tb group by convert(varchar(10),收发日期,120)
set @sql = @sql + ' from tb group by 物码及规格名称, 单位'
exec(@sql)
http://blog.csdn.net/qianjin036a/article/details/6582237
sum(convert(数值类型,varchar字段)) ?
set @sql = 'select 物码及规格名称, 单位'
select @sql = @sql + ' , sum(case convert(varchar(10),收发日期,120) when '''
+convert(varchar(10),收发日期,120)+ ''' then 收发数量 else 0 end) [' + convert(varchar(10),收发日期,120) + ']'
from tb group by convert(varchar(10),收发日期,120)
set @sql = @sql + ' from tb group by 物码及规格名称, 单位'
exec(@sql)
select * from (SELECT (rtrim(j_wzbm)+' '+rtrim(w_wzmc)+' '+rtrim(w_ggxh)) as wmgg,w_jldw as jldw,Ltrim(RTrim(STR(sum(j_sl), 20, 3))) as sfsl,j_date as sfrq FROM ( select * from ( select * from (select * FROM toperqx LEFT OUTER JOIN Tjhhqb ON left(Tjhhqb.j_wzbm,2)=toperqx.c_qx) lsdaywork LEFT OUTER JOIN twzzl ON lsdaywork.j_wzbm=twzzl.w_wzbm where lsdaywork.j_jgid='01' and lsdaywork.c_qxtag='1' and lsdaywork.c_cid='019' and lsdaywork.j_date>='2011-08-26' and lsdaywork.j_date<='2011-09-26' and (left(lsdaywork.j_wzbm,2)='08' or left(lsdaywork.j_wzbm,2)='10') ) daywork ) cccc group by j_date,w_wzmc,w_ggxh,w_jldw) ccc
select * into #tb--临时表 #tb
from (SELECT (rtrim(j_wzbm)+' '+rtrim(w_wzmc)+' '+rtrim(w_ggxh)) as wmgg,w_jldw as jldw,Ltrim(RTrim(STR(sum(j_sl), 20, 3))) as sfsl,j_date as sfrq FROM ( select * from ( select * from (select * FROM toperqx LEFT OUTER JOIN Tjhhqb ON left(Tjhhqb.j_wzbm,2)=toperqx.c_qx) lsdaywork LEFT OUTER JOIN twzzl ON lsdaywork.j_wzbm=twzzl.w_wzbm where lsdaywork.j_jgid='01' and lsdaywork.c_qxtag='1' and lsdaywork.c_cid='019' and lsdaywork.j_date>='2011-08-26' and lsdaywork.j_date<='2011-09-26' and (left(lsdaywork.j_wzbm,2)='08' or left(lsdaywork.j_wzbm,2)='10') ) daywork ) cccc group by j_date,w_wzmc,w_ggxh,w_jldw) cccdeclare @sql varchar(8000)
set @sql = 'select 物码及规格名称, 单位'
select @sql = @sql + ' , max(case convert(varchar(10),收发日期,120) when '''
+convert(varchar(10),收发日期,120)+ ''' then 收发数量 end) [' + convert(varchar(10),收发日期,120) + ']'
from #tb group by convert(varchar(10),收发日期,120)
set @sql = @sql + ' from #tb group by 物码及规格名称, 单位'
exec(@sql)
--用完删除#tb
drop table #tb
select * into #tb--临时表 #tb
from (SELECT j_wzbm as wzbm,(rtrim(j_wzbm)+' '+rtrim(w_wzmc)+' '+rtrim(w_ggxh)) as wmgg,w_jldw as jldw,Ltrim(RTrim(STR(j_dj, 20, 2))) as wzdj,Ltrim(RTrim(STR(sum(j_sl), 20, 3))) as sfsl,Ltrim(RTrim(STR(sum(round(convert(numeric(38,3),j_sl*j_dj),2)), 20, 2))) as sfje,j_date as sfrq FROM ( select * from ( select * from (select * FROM toperqx LEFT OUTER JOIN Tjhhqb ON left(Tjhhqb.j_wzbm,2)=toperqx.c_qx) lsdaywork LEFT OUTER JOIN twzzl ON lsdaywork.j_wzbm=twzzl.w_wzbm where lsdaywork.j_jgid='01' and lsdaywork.c_qxtag='1' and lsdaywork.c_cid='019' and lsdaywork.j_date>='2011-08-26' and lsdaywork.j_date<='2011-09-26' and (left(lsdaywork.j_wzbm,2)='08' or left(lsdaywork.j_wzbm,2)='10') ) daywork ) cccc group by j_date,j_wzbm,w_wzmc,w_ggxh,w_jldw,j_dj) ccc
declare @sql varchar(8000)
set @sql = 'select wmgg, jldw'
select @sql = @sql + ' , max(case convert(varchar(10),sfrq,120) when '''
+convert(varchar(10),sfrq,120)+ ''' then sfsl end) [' + convert(varchar(10),sfrq,120) + ']'
from #tb group by convert(varchar(10),sfrq,120)
set @sql = @sql + ' from #tb group by wmgg, jldw'
exec(@sql)提示如下提示
服务器: 消息 8120,级别 16,状态 1,行 5
列 '#tb.sfrq' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
服务器: 消息 8120,级别 16,状态 1,行 5
列 '#tb.sfrq' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
set @sql = 'select 物码及规格名称, 单位'
select @sql = @sql + ' , max(case convert(varchar(10),收发日期,120) when '''
+convert(varchar(10),收发日期,120)+ ''' then 收发数量 end) [' + convert(varchar(10),收发日期,120) + ']'
from #tb group by convert(varchar(10),收发日期,120)
set @sql = @sql + ' from #tb group by 物码及规格名称, 单位'
exec(@sql)
set @sql = 'select 物码及规格名称, 单位'
select @sql = @sql + ' , max(case convert(varchar(10),收发日期,120) when '''
+convert(varchar(10),收发日期,120)+ ''' then 收发数量 end) [' + convert(varchar(10),收发日期,120) + ']'
from #tb group by convert(varchar(10),收发日期,120)
set @sql = @sql + ' from #tb group by 物码及规格名称, 单位'
exec(@sql)