Re: Multiple selects in one

From: The Magnet <art_at_unsu.com>
Date: Fri, 12 Mar 2010 08:29:54 -0800 (PST)
Message-ID: <d3f64ba8-6a1c-43c6-8906-c9583533d84f_at_v20g2000yqv.googlegroups.com>

On Mar 12, 6:19 am, Randolf Geist <mah..._at_web.de> wrote:
> On Mar 11, 3:51 pm, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > Sorry, version 10g R2.
>
> > As for DDL / DML, not sure why that helps.  Just a table with a couple
> > of records:
>
> > SUBSCRIPTIONS
> > -------------
> > CUSTOMER_ID         12345
> > PRODUCT_ID          203
> > SUBSCRIPTION_ID     6767
> > STATUS              2
>
> > CUSTOMER_ID         12345
> > PRODUCT_ID          204
> > SUBSCRIPTION_ID     6768
> > STATUS              1
>
> > Now, to get those into 1 display record:
>
> > CUSTOMER_ID  PRODUCT_ID  SUBSCRIPTION_ID  STATUS  PRODUCT_ID
> > SUBSCRIPTION_ID  STATUS
> > 12345        203         6767             2       204
> > 6768             1
>
> > And if one side is not true:
>
> > CUSTOMER_ID  PRODUCT_ID  SUBSCRIPTION_ID  STATUS  PRODUCT_ID
> > SUBSCRIPTION_ID  STATUS
> > 12345                                             204
> > 6768             1
>
> I think what you're looking for is commonly called a PIVOT operation
> that turns rows into columns. Oracle 11g introduced the PIVOT
> operator, but it can be done with pre-11g versions as well by using a
> GROUP BY with a bit cumbersome aggregate function expression.
>
> Something like this should give you a starting point:
>
> with subscriptions as (
> select
>          12345 as CUSTOMER_ID
>        , 203   as PRODUCT_ID
>        , 6767  as SUBSCRIPTION_ID
>        , 2     as STATUS
> from
>          dual
> union all
> select
>          12345 as CUSTOMER_ID
>        , 204   as PRODUCT_ID
>        , 6768  as SUBSCRIPTION_ID
>        , 1     as STATUS
> from
>          dual
> )
> SELECT
>         customer_id
>       , non_ut_subscr_id
>       , non_ut_product_id
>       , non_ut_status
>       , ut_subscr_id
>       , ut_product_id
>       , ut_status
>   FROM
>         (
>         SELECT
>                 customer_id
>               , max(decode(product_id, 204, null, subscription_id)) as
> non_ut_subscr_id
>               , max(decode(product_id, 204, null, product_id))      as
> non_ut_product_id
>               , max(decode(product_id, 204, null, status))          as
> non_ut_status
>               , max(decode(product_id, 204, subscription_id, null)) as
> ut_subscr_id
>               , max(decode(product_id, 204, product_id, null))      as
> ut_product_id
>               , max(decode(product_id, 204, status, null))          as
> ut_status
>         FROM
>                 subscriptions
>         WHERE
>                 customer_id = 12345
>         GROUP BY
>                 customer_id
>         )
> ;
>
> If you're interested what 11g offers, you can lookup the new PIVOT
> syntax in this excellent article by OakTable fellow Adrian Billington:http://www.oracle-developer.net/display.php?id=506
>
> Regards,
> Randolf
>
> Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> Co-author of the "OakTable Expert Oracle Practices" book:http://www.apress.com/book/view/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...

Interesting query. It works. I never really understood the 'WITH' queries. Reading on it did not really help either.

Thanks. Received on Fri Mar 12 2010 - 10:29:54 CST