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

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"

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 CET