本文介绍了使用多列的数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想要结果这个...
Hi,
I want result this...
Date Branch1 Branch2 Branch3 Total
Qty Value Qty Value Qty Value Qty Value
15-Nov-13 5 5000 5 5000 5 5000 15 15000
16-Nov-13 5 5000 5 5000 5 5000 15 15000
17-Nov-13 5 5000 5 5000 5 5000 15 15000
18-Nov-13 5 5000 5 5000 5 5000 15 15000
19-Nov-13 5 5000 5 5000 5 5000 15 15000
20-Nov-13 5 5000 5 5000 5 5000 15 15000
推荐答案
USE[cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_OZ_notevenPivot](
[Date][datetime],
[Branch1_Qty][int],
[Branch1_Value][bigint],
[Branch2_Qty][int],
[Branch2_Value][bigint],
[Branch3_Qty][int],
[Branch3_Value][bigint]
)
INSERT INTO [cpqa].[tbl_OZ_notevenPivot]
VALUES('15-Nov-13',5,5000,5,5000,5,5000),
('16-Nov-13',5,5000,5,5000,5,5000),
('17-Nov-13',5,5000,5,5000,5,5000),
('18-Nov-13',5,5000,5,5000,5,5000),
('19-Nov-13',5,5000,5,5000,5,5000)
首先作为 - 是查询,给出可疑数据:
_
_
First as-is query, given suspected data:
_
_
SELECT * FROM [cpqa].[tbl_OZ_notevenPivot]
Date Branch1_Qty Branch1_Value Branch2_Qty Branch2_Value Branch3_Qty Branch3_Value
2013-11-15 00:00:00.000 5 5000 5 5000 5 5000
2013-11-16 00:00:00.000 5 5000 5 5000 5 5000
2013-11-17 00:00:00.000 5 5000 5 5000 5 5000
2013-11-18 00:00:00.000 5 5000 5 5000 5 5000
2013-11-19 00:00:00.000 5 5000 5 5000 5 5000
然后以表格形式怀疑答案数据:
_
_
Then suspect answer data in table form:
_
_
SELECT [Date],[Branch1_Qty],[Branch1_Value],[Branch2_Qty],[Branch2_Value],[Branch3_Qty],[Branch3_Value], Branch1_Qty + Branch2_Qty + Branch3_Qty AS [Total_Qty], Branch1_Value + Branch2_Value + Branch3_Value AS [Total_Value] FROM [cpqa].[tbl_OZ_notevenPivot]
Date Branch1_Qty Branch1_Value Branch2_Qty Branch2_Value Branch3_Qty Branch3_Value Total_Qty Total_Value
2013-11-15 00:00:00.000 5 5000 5 5000 5 5000 15 15000
2013-11-16 00:00:00.000 5 5000 5 5000 5 5000 15 15000
2013-11-17 00:00:00.000 5 5000 5 5000 5 5000 15 15000
2013-11-18 00:00:00.000 5 5000 5 5000 5 5000 15 15000
2013-11-19 00:00:00.000 5 5000 5 5000 5 5000 15 15000
这篇关于使用多列的数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!