Re: SQL sentence

From: Facun Chamut <facunchamut_at_gmail.com>
Date: 21 Jul 2005 13:43:00 -0700
Message-ID: <1121978580.897520.142750@g43g2000cwa.googlegroups.com>

MĒ Carmen Lozano wrote:
> I have a table in oracle with information similar to this:
>
> city code
> A XX
> B SS
> A ZZ
> C YY
> D XX
> B SS
> B ZZ
>
> I need to preferably make by means of a sentence SQL or by
> means of a process a table with the following structure:
>
> city SS XX YY ZZ
> A 0 1 0 1
> B 2 0 0 1
> C 0 0 1 0
> D 0 1 0 0
>
> Can somebody help me?
>
> A greeting and thank you very much
>
> Noriepa

Try this. Add as many UNION statements as codes you have/columns you want.

select city, sum(SS), sum(XX), sum(YY)
from (
select city

      ,count(*) as SS
      ,0 as XX
      ,0 as YY

from table
where code = 'SS'
UNION
select city

      ,0 as SS
      ,count(*) as XX
      ,0 as YY

from table
where code = 'XX'
UNION
select city

      ,0
      ,0 as XX
      ,count(*) as YY

from table
where code = 'YY'
) group by city

-Facundo. Received on Thu Jul 21 2005 - 15:43:00 CDT