有点难：sql行列转换并汇总计算

wongwhbbo 2018-08-16 04:22:17

请大神帮助





CREATE TABLE [dbo].[#tmp_aa](

    [ftype] varchar(255),

	[fdate] varchar(255),

	

	[FHeadSelfB0436] [varchar](255) NULL,

	[fqty] [decimal](28, 10)  NULL

	

) 



insert into #tmp_aa

select '领料','2018-08-02','CMT-Harness-PM-Line01',156.0500000000 union all

select '领料','2018-08-02','CMT-Harness-PM-Line01',134.0500000000 union all



select '领料','2018-08-03','CMT-Harness-PM-Line01',100 union all

select '领料','2018-08-04','CMT-Harness-PM-Line01',100 union all

select '领料','2018-08-05','CMT-Harness-PM-Line01',100 union all

select '报废','2018-08-02','CMT-Harness-PM-Line01',50 union all

select '报废','2018-08-02','CMT-Harness-PM-Line01',50 union all



select '报废','2018-08-04','CMT-Harness-PM-Line01',100 union all

select '报废','2018-08-05','CMT-Harness-PM-Line01',100 union all





select '领料','2018-08-02','CMT-Harness-PM-Line02',156.0500000000 union all

select '领料','2018-08-02','CMT-Harness-PM-Line02',134.0500000000 union all



select '领料','2018-08-03','CMT-Harness-PM-Line02',100 union all

select '领料','2018-08-04','CMT-Harness-PM-Line02',100 union all

select '领料','2018-08-05','CMT-Harness-PM-Line02',100 union all

select '报废','2018-08-02','CMT-Harness-PM-Line02',50 union all

select '报废','2018-08-02','CMT-Harness-PM-Line02',50 union all



select '报废','2018-08-04','CMT-Harness-PM-Line02',100 union all

select '报废','2018-08-05','CMT-Harness-PM-Line02',100



DECLARE @s NVARCHAR(4000)  

SELECT @s = ISNULL(@s + ',', '') +  QUOTENAME(fdate)  

FROM  (select distinct fdate from #tmp_aa ) as A order by fdate ---列名不要重复  

  

Declare @sql NVARCHAR(4000)  

SET @sql='  

 select r.* from  

(select FHeadSelfB0436,ftype,fqty,fdate from #tmp_aa) as t  

pivot  

(  

sum(t.fqty)  

for t.fdate in ('+@s+')  

) as r order by FHeadSelfB0436,ftype '  

   

EXEC( @sql)

希望达到的效果：

FHeadSelfB0436 ftype 2018/8/2 2018/8/3 2018/8/4 2018/8/5
CMT-Harness-PM-Line01 报废 100 0 100 100
CMT-Harness-PM-Line01 领料 290.1 100 100 100
报废率 34.47087211 25.6344527 40.80799837 50.83884087
CMT-Harness-PM-Line02 报废 90 0 100 100
CMT-Harness-PM-Line02 领料 290.1 100 100 100
报废率 31.0237849 23.07100743 38.76759845 49.14421285
报废率汇总计算的公式：2018/8/2的报费率=（2018/8/2报废数）/2018/8/2领用数*100
2018/8/3:（2018/8/2报废数+2018/8/3报废数）/（2018/8/2领用数+2018/8/3领用数）*100
2018/8/4:（2018/8/2报废数+2018/8/3报废数+2018/8/4报废数）/（2018/8/2领用数+2018/8/3领用数+2018/8/4领用数）*100
后面以此类推
难点：报废率的横向统计计算不知如何实现，请大神帮助，谢

...全文

399 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

wongwhbbo 2018-09-01

打赏
举报

非常感谢兄的帮助，但sql运行有错误

wongwhbbo 2018-08-17

打赏
举报

请sql大神提供一个思路，谢

weixin_41779699 2018-08-17

打赏
举报

with tbl1
as
(
select FHeadSelfB0436,ftype,fdate,sum(fqty) fqty
from #tmp_aa
group by FHeadSelfB0436,ftype,fdate
union all
select distinct FHeadSelfB0436,b.ftype,fdate,0 as fqty from #tmp_aa a cross join ( select N'领料' as ftype union all select N'报废') b
),
tbl2
as
(
select distinct FHeadSelfB0436,fdate,ftype,sfqty,case ftype when N'报废' then 1 else 2 end as sort
from tbl1 a
cross apply (select sum(fqty) sfqty from tbl1 b where a.FHeadSelfB0436 = b.FHeadSelfB0436 and a.ftype = b.ftype and b.fdate <= a.fdate group by FHeadSelfB0436,ftype) c
)
select * into #tmp_bb
from
(
select * from tbl2
union all
select a.FHeadSelfB0436,a.fdate,N'报废率',a.sfqty/b.sfqty,3 as sort from tbl2 a,tbl2 b where a.FHeadSelfB0436 = b.FHeadSelfB0436 and a.fdate = b.fdate and a.ftype =N'报废' and b.ftype =N'领料'
) d

DECLARE @s NVARCHAR(4000)
SELECT @s = ISNULL(@s + ',', '') + QUOTENAME(fdate)
FROM (select distinct fdate from #tmp_bb ) as A order by fdate ---列名不要重复

Declare @sql NVARCHAR(4000)
SET @sql='
select rc.* from
(select FHeadSelfB0436,ftype,sfqty,fdate,sort from #tmp_bb) as t
pivot
(
sum(t.sfqty)
for t.fdate in ('+@s+')
) as rc order by FHeadSelfB0436,sort'

EXEC( @sql)

weixin_41779699 2018-08-17