22,209
社区成员
发帖
与我相关
我的任务
分享
Select pno [产品],Convert(varchar(100), planDate,11) [日期],
SUM(planQty) [计划数],
SUM(Isnull(injQty,0)) [生产数],
SUM(Isnull(packQty,0)) [入库数],
SUM(planQty-Isnull(injQty,0)) [偏差数]
From t_injPOhis
WHERE planDate between '2013-7-1' AND '2013-7-31'
Group By pno,planDate
Order By pno,planDate
Select pno [产品],Convert(varchar(100), planDate,11) [日期],
SUM(planQty) [计划数],
SUM(Isnull(injQty,0)) [生产数],
SUM(Isnull(packQty,0)) [入库数],
SUM(planQty-Isnull(injQty,0)) [偏差数]
into #t
From t_injPOhis
WHERE planDate between '2013-7-1' AND '2013-7-31'
Group By pno,planDate
Order By pno,planDate
-- 造测试数据
create table #t
([产品] varchar(20),
[日期] varchar(12),
[计划数] int,
[生产数] int,
[入库数] int,
[偏差数] int
)
insert into #t
select 'J001PJ60F5402', '13/07/02', 45000, 0, 0, 45000 union all
select 'J001PJ60F5402', '13/07/08', 40000, 0, 0, 40000 union all
select 'J001RJ8006101', '13/07/01', 45000, 43200, 72700, 1800 union all
select 'J001RJ8006101', '13/07/07', 45000, 43844, 40000, 1156
declare @tsql varchar(6000)
select @tsql='with t as
(select [产品],t ''类型'',[日期],v
from #t a
unpivot(v for t in([计划数],[生产数],[入库数],[偏差数])) up
)
select [产品],cast([类型] as varchar(16)) [类型],'+stuff((select ',isnull(['+[日期]+'],0) '''+[日期]+''' ' from (select distinct [日期] from #t) t for xml path('')),1,1,'')
+' from t b
pivot(max(v) for [日期] in('+stuff((select ',['+[日期]+']' from (select distinct [日期] from #t) t for xml path('')),1,1,'')+ ') ) p
order by [产品] '
exec(@tsql)
/*
产品 类型 13/07/01 13/07/02 13/07/07 13/07/08
-------------------- ---------------- ----------- ----------- ----------- -----------
J001PJ60F5402 计划数 0 45000 0 40000
J001PJ60F5402 偏差数 0 45000 0 40000
J001PJ60F5402 入库数 0 0 0 0
J001PJ60F5402 生产数 0 0 0 0
J001RJ8006101 计划数 45000 0 45000 0
J001RJ8006101 偏差数 1800 0 1156 0
J001RJ8006101 入库数 72700 0 40000 0
J001RJ8006101 生产数 43200 0 43844 0
(8 row(s) affected)
*/