带有4列的SQL Server 2008数据透视表 [英] sql server 2008 pivot of table with 4 columns

问题描述

这是我的桌子.

this is my table.

Month	Revenue	Tax	Year
Jan	5	2	2011
Feb	3	4	2011
Mar	45	65	2011
April	74	8	2011
May	14	32	2011
June	19	6	2011
Jan	28	498	2012
Feb	5	123	2012

我想要以下格式的数据透视表

I want Pivoted table in the following format

Month	2011		2012	
	Revenue	Tax	Revenue	Tax
Jan	5	2	28	498
Feb	3	4	5	123
Mar	45	65		
April	74	8		
May	14	32		
June	19	6		

有可能吗?

我将感谢您的解决方案.
Thnx

is it possible.

I will appreciate the solutions.
Thnx

推荐答案

您应该创建标题，例如"Revenue-2011"，"Tax-2011"，"Revenue-2012"，"Tax-2012". > 您即将到达要使用3D数据而不是2D数据的地步...

You should create headers like "Revenue-2011", "Tax-2011", "Revenue-2012", "Tax-2012".
You are coming to a point you want to go to 3D data in stead of 2D data...

是可能的！
但需要处理查询，如果要在Crystal Report中使用，则很容易在其他方面使用，您需要将代码C#(DATATABLE)和sql server(UNION，SELECT)组合在一起

！

yes its possible !
but need to play with query ,if you use in crystal report then its easy other wise you need to combine code C#(DATATABLE) and sql server(UNION,SELECT)

!

嘿
试试这个不完全正确的方法，但是在您的答案工作上做得很少，您会得到答案的
WITH tempWith AS

Hey
try this not exact but close to your answer work little on it u will get your answer
WITH tempWith AS

(SELECT TMonth,TYear,
CASE TYear
WHEN '2011' THEN Revenue END AS [2011Revenue],
CASE TYear
WHEN '2011' THEN Tax end as [2011Tax] ,
CASE TYear
WHEN '2012' THEN Revenue END AS [2012Revenue],
CASE TYear
WHEN '2012' THEN Tax END AS [2012Tax]
FROM dbo.TaxDetail)

SELECT TMonth,	MAX([2011Revenue]) AS [2011 Revenue],
			    MAX([2011Tax])AS [2011 Tax],
				MAX([2012Revenue]) AS [2012 Revenue],
				MAX([2011Tax])AS [2012 Tax]

来自tempWith
GROUP BY TMonth

FROM tempWith
GROUP BY TMonth

这篇关于带有4列的SQL Server 2008数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！