Rotating Data (crosstab)

[jhall01]

I have this query that pulls this info:

accountID service effdate servicefee rank
111 abc 10/10/03 10.00 1
111 bcd 10/23/03 20.00 2
222 abc 11/01/01 20.00 1
222 bcd 11/01/01 10.00 2
222 xyz 12/10/02 30.00 3
333 rst 01/01/03 95.00 1

RANK is setup in the query for ranking muliple ID's based on effdate desc, fee desc grouped by accountID
i want to rotate it out into this form:

ID service1 effdate1 fee1 service2 effdate2 fee2...
111 abc 10/10/03 10.00 bcd 10/23/03 20.00
etc.

I use something close to this:

SELECT
ID,
CASE WHEN rank = 1 then service END service1
CASE WHEN rank = 1 then effdate1 END effdate1
...
CASE WHEN rank = 2 then service END service2
...
FROM
mytable
GROUP BY 1
ORDER BY 1

Problem that I have is I can't group by ID to get my data truely rotated due to "Selected non-aggregate values must be part of the associated group."

If i don't group then each rank is a new group and doesnt combine the rotated records. I tried taking the max of the case statements, but it takes the max value of the date across all 3 service columns and not the different effdates.

Thanks,
Jon

 

[dnoeth]

You need to MAX each CASE:

MAX(CASE WHEN rank = 1 then service END) service1
MAX(CASE WHEN rank = 1 then effdate1 END) effdate1
...
MAX(CASE WHEN rank = 2 then service END) service2

Dieter

 

[jhall01]

Yes i have done that. Max each case, but for some reason it will take the max date of all the records and put it in all 3 columns. I think there is something wrong with my case criteria

it always takes the latest date when i max the effdate and places it in all 3 effdates

 

[dnoeth]

Could you post the actual query?

Dieter

 

[jhall01]

I figured out the problem. I had to max each case, but also changes some of the case criteria. and use a rank() instead of csum()to do my grouping in a subquery.

I would have posted the code but I would have had to mask alot of the information because of proprietary info