Re: Impossible SQL Task ??

From: Lothar Armbrüster <lothar.armbruester_at_rheingau.netsurf.de>
Date: 30 Apr 99 20:36:30 +0100
Message-ID: <1399.789T1520T12363811lothar.armbruester@rheingau.netsurf.de>

Matt Randle wrote on 30-Apr-99 17:11:28:
>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.

Hello Matt,

try this:

select

   t1.id,
   t1.identifier a,
   t2.identifier b

from

   your_table t1,
   your_table t2
where

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

Well, it's not very elegant but it should do the job. If your table is big, be sure to create useful indexes and check the execution plan. :-)

>Cheers,

>Matt.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |

Received on Fri Apr 30 1999 - 14:36:30 CDT