SQL Question for Scott.Emps

[Quehay]

I'm working through an Oracle training manual. There's a exercise that asks for output like this:

Total Hires 1980 1981 1982
45 10 25 10

This is supposed to come from Emps. The book hasn't got to Nested columns yet (and I haven't learned this yet). I think the only way to get this is with subqueries hard coded for WHERE substr(HireDate,-2) = 80 etc.

Of course row, rather than column output is easy.
Select substr(HireDate,-2) "Year", Count(HireDate) "Number of Hires"
From Emp
Group by substr(HireDate,-2);

Is there some trick to getting columns for diffent "Grouping" values that I'm missing?

 

[petersJazz]

This kind of question isnt the strengt of sql. In Oracle you can do:

select id,
sum(decode(year, 1980, value, 0)),
sum(decode(year, 1981, value, 0))
from table
group by id;

but its sort of hardcoded. Other products have other things than decode.

 

[petersJazz]

By the way: there is an Oracle specific forum.

 

[Quehay]

Thanks! This would give the year-specific columns, but not the headers (unless you can alias in each decode line). I think this was a bogus question but I didn't want to write it off before looking for an answer that I'd missed.

(This was more of a SQL question than "How do set block size for fastest I/O writes?", etc.)