Re: using SQL to create a "cross-table" report

From: <Shane>
Date: 1997/01/22
Message-ID: <32e58c53.263786881@news>#1/1

On Tue, 21 Jan 1997 16:35:40 -0800, Charanjeet <charan.j_at_midas-it.co.uk> wrote:

Oh yea... Cross-tab queries. They most certainly can be done via Oracle SQL. Use the decode statement. Hey, you didn't say it had to be pretty (or fast)!!!

I have data similar to what you diagramed below:

code
year
amount

The data is stored relationally, but users like it cross-tabbed like this:

code 		yr1	yr2 	yr3
A		100	200	300
B		200	400	600

select 
	code, 
	decode(yr,yr1,amnt,0) "yr1", 
	decode(yr,yr2,amnt,0) "yr2", 
	decode(yr,yr3,amnt,0) "yr3"

from table;

code needs to be unique in the underlying table, or you will need to "roll" up the values per code first with a group by.

Hope this helps. Received on Wed Jan 22 1997 - 00:00:00 CST