PIVOT FOR 2 or more Columns

Question

PIVOT FOR 2 or more Columns

ßhavin

Old Hand

Points: 357
More actions
March 27, 2010 at 12:53 am

#139048

Hi all.
I have developed CTE which gives result using PIVOT; Its working perfect. as follows.
Item Year Jan Feb Mar ...
---------------------------------------------------------
ABC 2009 290 340 450 123
PQR 2008 120 344 200 450
Now I want an additional column which is Stardard with every month. That is..
Item Year Jan Std Feb Std Mar Std ...
---------------------------------------------------------
ABC 2009 290 299 340 355 450 510 123
PQR 2008 120 340 344 340 200 198 450
The Query is
WITH CET1
(
)
SELECT
*
FROM
(
How to solve this?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

It would help if you can give us some sample data and the table structure. It would be difficult to help you with the given information.

From whatever i understood, I would suggest you use a Cross Tab for the problem. That would be a lot easier to understand and hopefully faster.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 2

Bhavin_Bhatt25 (3/27/2010)
Hi all.
I have developed CTE which gives result using PIVOT; Its working perfect. as follows.
Item Year Jan Feb Mar ...
---------------------------------------------------------
ABC 2009 290 340 450 123
PQR 2008 120 344 200 450
Now I want an additional column which is Stardard with every month. That is..
Item Year Jan Std Feb Std Mar Std ...
---------------------------------------------------------
ABC 2009 290 299 340 355 450 510 123
PQR 2008 120 340 344 340 200 198 450
The Query is
WITH CET1
(
)
SELECT
*
FROM
(
How to solve this?

Please see the following article which has both a CrossTab and a Pivot example for that which you seek.

http://www.sqlservercentral.com/articles/T-SQL/63681/

To make maintenance of your code a bit less, a dynamic solution may be in order. Please see the following article for that...

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)