This is actually pretty hard. The best I could come up with is
select id, A = case name when 'A' then count(*) else 0 end,
B = case name when 'B' then count(*) else 0 end,
C = case name when 'C' then count(*) else 0 end
from test
group by id, name
which doesn't really do what you want. I'm sure you could work out
something eventually, but that would probably take a long time. My
suggestion would be to actually create a temp table and use that instead.
Something like
Create table temp_a(marca int4,
condceddi int4,
boala int4,
obligatii int4)
insert into temp_a (marca)
select distinct marca
from a
update temp_a set condceddi = (select count(ma) from a as a
where a.id = temp_a.id
and name like 'A')
update temp_a set boaloa = (select count(ma) from a as a
where a.id = temp_a.id
and name like 'B')
update temp_a set obligatti = (select count(ma) from a as a
where a.id = temp_a.id
and name like 'C')
select * from temp_a
and then drop the table (or, better yet, create a temp table - but I'm not
sure how to do this in postgres).
If anyone knows a better way, I would be most interested to know.
---
Rob
**************************
Rob Cherry
mailto:rob@jamwarehouse.com
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of cristi
> Sent: 12 November 2002 08:46
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] SQL
>
>
> I have a table a:
> create tabel a( marca int4,
> mo varchar(1)
> );
>
> This is the contents of the table:
> marca mo
> 1 C
> 2 C
> 4 B
> 5 O
> 1 C
> 1 B
>
> I need a SQL interogation with following results:
>
> marca concedii boala obligatii
> 1 2 1 0
> 2 1 0 0
> 4 0 1 0
> 5 0 0 1
>
> How can I do that?
>
>
> Thanks!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
