Re: Multiple selects in one

From: Randolf Geist <mahrah_at_web.de>
Date: Fri, 12 Mar 2010 04:19:42 -0800 (PST)
Message-ID: <b5a26637-c97d-4e63-ae46-ad39b015b610_at_i25g2000yqm.googlegroups.com>

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/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Fri Mar 12 2010 - 06:19:42 CST