Group by/Order by Pivoting problem.

From: TheSpongebob <gastcom_at_sympatico.ca>
Date: 6 Apr 2006 09:49:32 -0700
Message-ID: <1144342172.716059.268530@i39g2000cwa.googlegroups.com>

I am "pivoting" multiple years of data by key. I want to ensure year1 is really the 1st row, so I want to order by Date but it can't be part of my group by clause.

tableA

key1	date1	amt
abc	2004	100
abc	2003	110
abc	2002	120

bbc	2004	220
bbc	2003	230
bbc	2002	240

expected output

	cur_yr	prior_yrs  prior_prior_yr
abc	100 	110 	   120
bbc	220 	230 	   240

I want to group by key1. I want to sort by date1 but I don't want to group by date1 ... this wrecks my grouping. Is it possible to sort without appearing in the grouping clause?

Aside
(
I also tried sorting tableA as an inline table however I was unable to 'attach' Key1 to anything external.
)

Any tips ? Received on Thu Apr 06 2006 - 11:49:32 CDT