Pivot table

Question

Post reply

Pivot table

nikkikapor

Grasshopper

Points: 24
More actions
September 9, 2009 at 8:06 pm

#137998

Hi,
This is a great forum.
But i'm having problems to do this in my query as i want the output for this query in cross tab using pivot table command
I have:
city sales 1stweeksaleorder 1weeksaledelivered salein% 2ndweeksalesordered 2weeksales salesin%
NJ 1000 400 300 75% 600 500 83%
PA 800 300 150 50% 400 300 75%
TX 200 100 85 85% 700 600 85%
Can pls anyone tell me how to use pivot command for this query so that i can have result in cross tab and how to use two different aggregation for this query

Viewing 4 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

The PIVOT code in the following article will do it...

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

... and, with any luck at all, it will convince you to never use PIVOT to do such a thing.

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

nikkikapor Grasshopper Points: 24 More actions · Answer 2

Hi Jeff,

Thanks for immediate reply for my problem .

But still i got a problem as you said that in article:

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

in Cross Tabs and Pivots, Part 1 – Converting Rows to Columns in Multi- Aggregate Pivot section they used the quarterly months(4 quarters) which are fixed but in my case i have more than 40 weeks which is variable. (like = 1st week,2ndweek,3rdweek and so on..... )

So could you help me how to use pivot query for this 40 weeks so that i can use this query to do cross tab reports.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

Sure... but, again, I wouldn't use PIVOT to do this. I would use pre-aggregated cross-tabs, instead. And the following article explains how to make them dynamically...

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

My apologies for not offering any code on this but I'm no where near an SQL Server and won't be for almost another week.

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