Click here to Skip to main content
15,303,572 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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		



is it possible.

I will appreciate the solutions.
Thnx
Posted

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...
   
Comments
Lousy Programmer 14-Mar-12 7:13am
   
@digmanus: had same thought earlier but that is not possible coz it will change architecutre of remaining system.
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)

!
   
Comments
Lousy Programmer 14-Mar-12 7:15am
   
@uuttam-kumar that was a good suggestion but I need SSRS only because those reports are needed to be uploaded in MS-CRM which supports only SSRS '.rdl' files
Hey
try this not exact but close to your answer work little on it u will get your answer
WITH tempWith AS
SQL
(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]

FROM tempWith
GROUP BY TMonth
   
v2
Have you tried Matrix control in SSRS..?
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900