Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Impossible SQL Task ??

Re: Impossible SQL Task ??

From: Marc Mazerolle <informaze_at_sympatico.ca>
Date: Fri, 30 Apr 1999 20:08:25 GMT
Message-ID: <372A0E74.9BB52703@sympatico.ca>

Matt Randle wrote:

> We have a list of IDs / Codes / Identifiers as follows,
>
> ID Code Identifier
>
> 1 A Matthew
> 1 B Randle
> 2 A Larry
> 2 B Ellison
> 3 A Dave
> 4 B Some Surname
>
> We need to re-orient the table so it comes back as,
>
> ID A B
>
> 1 Matthew Randle
> 2 Larry Ellison
> 3 Dave
> 4 Some Surname
>
> Is this possible in SQL ?? I can obviously do it programatically but it
> would be more elegent to do it with on SQL statement ?? I seem to remember
> reading about some 'projection' facility availiable some SQL server that
> does this sort of thing.
>
> Cheers,
>
> Matt.

I'll take a wack at it.....select t1.id, t1.a, t2.b from t t1, t t2

 where t1.id = t2.id
   and t1.code = 'A'
   and t2.code = 'B'

union allselect t2.id, null a, t2.b from t t2  where not exists (
       select 1
         from t t1
        where t1.id = t2.id
          and t1.code = 'A')

   and t2.code = 'B'
  union allselect t1.id, t1.a a, null b from t t1  where not exists (
       select 1
         from t t2
        where t2.id = t1.id
          and t2.code = 'B')

   and t1.code = 'A'

Do i pass the test ?

Regards,

Marc Mazerolle
InforMaze Technologies Received on Fri Apr 30 1999 - 15:08:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US