OraFAQ Forum: SQL & PL/SQL » Grouping Query

Home » SQL & PL/SQL » SQL & PL/SQL » Grouping Query (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Grouping Query [message #635795]

Thu, 09 April 2015 02:38

sasisan09
Messages: 15
Registered: October 2010
Location: India

Junior Member

Hi All,

I have a scenario where a customer can have base product (optional) and supplementary products. But the data will be residing in a row for each product instance. And there is a char field that says if the product is base product or not. Now my requirement it to get the data into a single row. Please find the details in the below code block.

create table test2 (customer_Ref varchar2(40),base_boo char(1),product_name varchar2(50),msisdn number);

insert into test2 (CUSTOMER_REF, BASE_BOO, PRODUCT_NAME, MSISDN)
values ('c1', 'y', 'Base', 9985);

insert into test2 (CUSTOMER_REF, BASE_BOO, PRODUCT_NAME, MSISDN)
values ('c1', 'n', 'Supp', 9985);

insert into test2 (CUSTOMER_REF, BASE_BOO, PRODUCT_NAME, MSISDN)
values ('c2', 'n', 'Supp', 9900);

Expected output is as follows :

CUSTOMER_REF	BASE_PRODUCT_NAME	SUPP_PRODUCT_NAME	MSISDN
c2	 	                              Supp	          9900
c1              Base	                      Supp	          9985

Currently I am achieving this by using the following query:

select customer_ref, max(base_product_name) as base_product_name, max(supp_product_name) as supp_product_name, msisdn
  from (
select customer_ref,
               decode (base_boo, 'y',product_name, ' ')  as base_product_name,
               decode (base_boo, 'n',product_name, ' ') as supp_product_name,
               msisdn
          from test2)  group by customer_ref, msisdn

Wanted to check if there is a more efficient way to achieve this. Also I would not be able to handle with my query if there are multiple supplementary products (which is not currently in requirement though).

Any help will be greatly appreciated.

Thanks
Sasidhar

Report message to a moderator

Re: Grouping Query [message #635799 is a reply to message #635795]

Thu, 09 April 2015 03:11

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Wanted to check if there is a more efficient way to achieve this.

This depends on many things like number of rows and indexes.
You can self-join the table one instance with the base product and the other with the non base products. This query will handle the case you currently do not.

Report message to a moderator

Re: Grouping Query [message #635940 is a reply to message #635795]

Mon, 13 April 2015 09:10

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

How about "PIVOT"?

SQL> WITH Test2 ( Customer_Ref
  2             , Base_Boo
  3             , Product_Name
  4             , Msisdn )
  5       AS (SELECT 'c1', 'y', 'Base', 9985 FROM DUAL UNION ALL
  6           SELECT 'c1', 'n', 'Supp', 9985 FROM DUAL UNION ALL
  7           SELECT 'c2', 'n', 'Supp', 9900 FROM DUAL)
  8  SELECT *
  9    FROM (  SELECT Customer_Ref
 10                 , Base_Boo
 11                 , Product_Name
 12                 , Msisdn
 13              FROM Test2 T
 14          ORDER BY Customer_Ref, Msisdn)
 15  PIVOT (MAX ( Product_Name )
 16      FOR Base_Boo
 17       IN ('y' AS Base_Product_Name
 18*        , 'n' AS Supp_Product_Name))
SQL> /

CUSTOM     MSISDN BASE_PRODUCT SUPP_PRODUCT
------ ---------- ------------ ------------
c1           9985 Base         Supp
c2           9900              Supp

Report message to a moderator

Re: Grouping Query [message #648986 is a reply to message #635940]

Wed, 09 March 2016 08:43

sasisan09
Messages: 15
Registered: October 2010
Location: India

Junior Member

Sorry for updating you all so late, We went with the following option :

Select SP.customer_ref,
       BP.product_name AS BASE_PRODUCT_NAME,
       SP.product_name AS SUPP_PRODUCT_NAME,
       SP.MSISDN
  from (select * from test2 where base_boo = 'y') BP,
       (select * from test2 where base_boo = 'n') SP
 where BP.customer_ref(+) = SP.customer_ref
   and BP.MSISDN(+) = SP.MSISDN
   order by SP.CUSTOMER_REF,SP.MSISDN,SP.PRODUCT_NAME

Regards
Sasidhar

Report message to a moderator

Re: Grouping Query [message #649012 is a reply to message #648986]

Wed, 09 March 2016 13:59

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

The following does not use any sub-queries, so might be a little simpler.

SCOTT@orcl> Select SP.customer_ref,
  2  	    BP.product_name AS BASE_PRODUCT_NAME,
  3  	    SP.product_name AS SUPP_PRODUCT_NAME,
  4  	    SP.MSISDN
  5    from test2 BP,
  6  	    test2 SP
  7   where BP.customer_ref(+) = SP.customer_ref
  8  	and BP.MSISDN(+) = SP.MSISDN
  9  	and BP.base_boo(+) = 'y'
 10  	and SP.base_boo = 'n'
 11  	order by SP.CUSTOMER_REF,SP.MSISDN,SP.PRODUCT_NAME
 12  /

CUSTOMER_REF BASE_PRODUCT_NAME        SUPP_PRODUCT_NAME            MSISDN
------------ ------------------------ ------------------------ ----------
c1           Base                     Supp                           9985
c2                                    Supp                           9900

2 rows selected.

Report message to a moderator

Re: Grouping Query [message #649013 is a reply to message #648986]

Wed, 09 March 2016 14:04

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Bad decision. You are scanning test2 twice:

select  customer_ref,
        max(
            case base_boo
              when 'y' then product_name
            end
           ) base_product_name,
        max(
            case base_boo
              when 'n' then product_name
            end
           ) supp_product_name,
        max(msisdn) msisdn
  from  test2
  group by customer_ref
/

CUSTOMER_REF BASE_PRODUCT_NAME SUPP_PRODUCT_NAME     MSISDN
------------ ----------------- ----------------- ----------
c1           Base              Supp                    9985
c2                             Supp                    9900

SQL>

SY.

Report message to a moderator

Previous Topic:	Understanding locking
Next Topic:	cursor logic

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 19:07:28 CDT 2024