Output columns from lowest to highest in a pivot type query help

Question

Output columns from lowest to highest in a pivot type query help

newmang

Grasshopper

Points: 14
More actions
April 2, 2010 at 9:15 am

#96647

I have a query (SQL 2000) which works fine:
Select
sum(case when a.[Month] = 1 then ([Avg Balance]) else 0 end) as [Jan Avg Bal] ,
sum(case when a.[Month] = 2 then ([Avg Balance]) else 0 end) as [Feb Avg Bal] ,
sum(case when a.[Month] = 3 then ([Avg Balance]) else 0 end) as [Mar Avg Bal] ,
sum(case when a.[Month] = 4 then ([Avg Balance]) else 0 end) as [Apr Avg Bal] ,
sum(case when a.[Month] = 5 then ([Avg Balance]) else 0 end) as [May Avg Bal] ,
sum(case when a.[Month] = 6 then ([Avg Balance]) else 0 end) as [Jun Avg Bal] ,
sum(case when a.[Month] = 7 then ([Avg Balance]) else 0 end) as [July Avg Bal] ,
sum(case when a.[Month] = 8 then ([Avg Balance]) else 0 end) as [Aug Avg Bal] ,
sum(case when a.[Month] = 9 then ([Avg Balance]) else 0 end) as [Sep Avg Bal] ,
sum(case when a.[Month] = 10 then ([Avg Balance]) else 0 end) as [Oct Avg Bal] ,
sum(case when a.[Month] = 11 then ([Avg Balance]) else 0 end) as [Nov Avg Bal] ,
sum(case when a.[Month] = 12 then ([Avg Balance]) else 0 end) as [Dec Avg Bal]
FROM tble a
GROUP BY a.[xxx], a.[yyyy]
Output Columns are JAN Feb, ...DEC regardless of year. The actual results are fine, just the columns
should go from lowest to highest Month/Date i.e DEC 2009 Jan 2010 Feb 2010 not Jab 2010 Feb 2010 Dec 2009
I need to output with lowest date (Dec 2009) to highest date (Feb 2010) :
Is that possible ?
Any help or pointers would be appreciated as I am not a sql programmer

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

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

newmang, I don't see anywhere you are telling the query which years to pull. Is there a collumn for year? If so, you might want to add 'AND a.year = 2009' to the December row, and 'AND a.year = 2010' for the rest of the months. As for the order of the months, they will appear in the order you specify in your query. So, if you want December to be first, then select it first.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

newmang Grasshopper Points: 14 More actions · Answer 2

i do have a [Year] attribute, so I will give this a shot. I did something like this, but didn't specify the actual year value.

Thanks!@

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

You'll need a bit of dynamic SQL to do what you want. Just so happens, I know a place that has an example...

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)

Garadin One Orange Chip Points: 29613 More actions · Answer 4

I've read several of that guys articles, good stuff. 😉

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 5

Garadin (4/2/2010)
I've read several of that guys articles, good stuff. 😉

Heh... thanks, Seth. Good finish to a long day.:-)

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