sql server 2008 pivot of table with 4 columns

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 14-Mar-12 1:03am

Lousy Programmer

Add a Solution

4 solutions

Solution 1

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...

Posted 14-Mar-12 1:11am

Herman<T>.Instance

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.

Solution 2

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)

!

Posted 14-Mar-12 1:11am

uttam2010

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

Solution 3

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

Posted 14-Mar-12 2:20am

Nilesh Patil Kolhapur

Updated 14-Mar-12 3:35am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sirivenu · Accepted Answer · 2012-03-19T14:11:00

Solution 4

Have you tried Matrix control in SSRS..?

Posted 19-Mar-12 14:11pm

sirivenu