Colums instead of rows

[michelleqw]

Dear sql users,

We want to group instead of rows now into colums.

Example

Tabel1:
income tax
100 0
200 1
10 1
100 0
12 0

The sql statement: “select tax, sum(income) as srt from tabel1 group by tax” gives:

srt income
0 212
1 210

We try to get:

tax-0 tax-1
212 210

Is there any possibility to get these colums? If yes can someone give us the sql statement or other advises?

Nice regards,

Michelle.

 

[michelleqw]

We tried:

select tax, sum(income) as tax0 from tabel1 where tax=0 group by tax
union
select tax, sum(income) as tax1 from tabel1 where tax=1 group by tax

This gave not the right result!

Michelle,

 

[PHV]

The basic idea:
SELECT
(select sum(income) from tabel1 where tax=0) AS tax0
,(select sum(income) from tabel1 where tax=1) AS tax1
FROM any table
WHERE any condition true for only one row

Check your documentation for CrossTab query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

an alternative:

select sum(case when tax=0 
                then income 
                else 0 end) as "tax-0"
     , sum(case when tax=1 
                then income 
                else 0 end) as "tax-1"
  from Tabel1

rudy

http://r937.com/

SQL Consulting

 

[michelleqw]

Thanks PHV

Michelle.