Re: Group by year, display years as columns?

On Thu, 26 May 2005 14:56:36 GMT, netcomradeNSPAM_at_bookexchange.net (NetComrade) wrote:

>All,
>
>Is there are a way do something like?
>
>select trunc(by_date, 'yyyy'), count(*)
>from my_table
>group by trunc(by_date,'yyyy')
>where by_date between last_year, this_year
>
>but to display the resultant years in columns (years will be limited,
>columns will be known in advance).
>
>1) is there are a straighforward way to do this?
>2) I recall I've read some transformation doc (how to make
>rows->columns) on metalink, but I can't find it

Apparently there are a few methods;

1. use decode such as: SELECT customer_id, SUM(DECODE(year, X-1, amount, 0)) prev_year_amount, SUM(DECODE(year, X, amount, 0)) year_X_amount FROM flat_data WHERE <quarter-month in given timescale> AND (year = X OR year = X-1)

 GROUP BY customer_id
2) procedural ways if # of columns are unkown 3) import to access will do it :) (since it has some transform function)

seeme like #1 is easiest.. but not super flexible.. probably with more complex analytical queries would become a pain.. .......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes remove NSPAM to email Received on Thu May 26 2005 - 10:47:57 CDT