Re: Impossible SQL Task ??

Yep. It is a little tricky if there can be an "A" with no "B" condition, a "B" with no "A" condition as well as a match.

If the "A" with no "B" condition will not happen, then a simple outer join can be used.

SELECT tbl_b.id, tbl_a.identifier A, tbl_b.identifier B   FROM table tbl_a, table tbl_b

  WHERE tbl_a.id(+) = tbl_b.id
    AND tbl_a.code (+) = 'A'
    AND tbl_b.code = 'B' ;

If the "A" with no "B" condition can happen, then adding a union will fill in that condition.

UNION
SELECT tbl_a.id, tbl_a.identifier A, NULL   FROM table tbl_a, table tbl_b

  WHERE tbl_a.id = tbl_b.id (+)
    AND tbl_b.code IS NULL
    AND tbl_a.code = 'A'
    AND tbl_b.code (+) = 'B' ;

HTH
    James

In article <925489117.23245.0.nnrp-06.d4e48d0d_at_news.demon.co.uk>,   "Matt Randle" <matt_at_imat.demon.co.uk> 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.
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Apr 30 1999 - 13:24:33 CDT