Multiple cols instead of rows

[graabein]

Hi all,

I have a question. I want my resulting table to have the cols "sum1" "sum2" "sum3" instead of a table with one column "tYear" and one column "tSum".

Example:

tYear tSum
2003 480
2002 270
2001 420

-->

sum1 sum2 sum3
480 270 420

This should be pretty easy.. but how do I do it?

Thanks,
graabein

 

[graabein]

Yes, one more thing, I want to specify which years I want for sum1..3!

 

[dnoeth]

select
sum(case when tyear = 2003 then tsum else 0 end) as sum1,
sum(case when tyear = 2002 then tsum else 0 end) as sum2,
sum(case when tyear = 2001 then tsum else 0 end) as sum3
from table;

If you want it dynamically created, it's more complicated...
Dieter

 

[swampBoogie]

select (select sum(c) from t where tYear = 2001) as sum1,
(select sum(c) from t where tYear = 2002) as sum2,
(select sum(c) from t where tYear = 2003) as sum3

It depends on which DBMS you are using if you can omit the from clause( it is not allowed in ANSI sql, which have a different syntax for just selecting scalar values but which is unsupported by most DBMS). If not, you can create a dummy table with just one row. Many systems have a predefined table for this such as dual in Oracle.

 

[graabein]

Thanks guys!