Oracle Server - Group by year, display years as columns?

Author

Group by year, display years as columns?

NetComrade

2005-05-26, 11:23 am

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

Thanks!
........
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
remove NSPAM to email

NetComrade

2005-05-26, 11:23 am

On Thu, 26 May 2005 14:56:36 GMT, netcomradeNSPAM@book
exchange.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

Maxim Demenko

2005-05-26, 11:23 am

NetComrade schrieb:
> 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
>
> Thanks!
> ........
> We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
> remove NSPAM to email

http://asktom.oracle.com/pls/ask/f?...58337
40,

Best regards

Maxim

Sponsored Links