Re: SQL

Creating the temp table is the easy part ... ;-)

create temp table mytable (...);

Evert Carton
Medical Research Laboratories
evertcarton(at)netscape(dot)net
evert(dot)carton(at)mrlinternational(dot)com

"Rob" <rob(at)jamwarehouse(dot)com> wrote:

>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(at)jamwarehouse(dot)com
>+27 21 447 7440
>Jam Warehouse RSA
>Smart Business Innovation
>http://www.jamwarehouse.com
>**************************
>
>
>> -----Original Message-----
>> From: pgsql-novice-owner(at)postgresql(dot)org
>> [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of cristi
>> Sent: 12 November 2002 08:46
>> To: pgsql-novice(at)postgresql(dot)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
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/