本文介绍了使用多列的数​​据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述





我想要结果这个...



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



这篇关于使用多列的数​​据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-20 12:18
查看更多