Re: Question on SELECT statement

From: Ricardo Rocha <rrocha_at_mindspring.com>
Date: 1997/08/21
Message-ID: <5tiiet$dhq@camel3.mindspring.com>#1/1

If all the possible values in the detail column are known in advance, you can write a query like the following:

SELECT
    Master.PrimKey,
    Detail.Otherkey,

    MIN(DECODE(Detail.column1, 100, column1, NULL)) AS c100,
    MIN(DECODE(Detail.column1, 200, column1, NULL)) AS c200,
    MIN(DECODE(Detail.column1, 300, column1, NULL)) AS c300,
    MIN(DECODE(Detail.column1, 400, column1, NULL)) AS c400

FROM
    Master,
    Detail
WHERE
    Detail.ForeignKey = Master.PrimKey
GROUP BY
    Master.PrimKey,
    Detail.Otherkey;

This would yield a result like:

PrimKey OtherKey c100 c200 c300 c400

-------------- ---------------- --------- ---------- ------ -------
1                HELLO                     200      300   400

Received on Thu Aug 21 1997 - 00:00:00 CDT