3 Replies Latest reply on Jan 16, 2012 10:01 PM by 723358

    Pivot help on an heirarchy query

      I've looked through the FAQs but I'm just not seeing it.
      I have the following:

      A query returning:
      o_id   oname            typ  prnt
      118    Pension Admin    PRC    91
      91     Retirement       LOB   218
      218    Benefits         SEG    81
      119    Plan Mgmt        PRC    91
      91     Retirement       LOB   218
      218    Benefits         SEG    81
      142    RCS Software     PRC    93
      93     Risk Software    LOB   221
      221    Risk Services    SEG    81
      I want the following:
      ID  PRC              LOB               SEG
      118 Pension Admin    Retirement        Benefits
      119 Plan Mgmt        Retirement        Benefits
      142 RCS Software     Risk Software     Risk Services

      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production
      CORE Production
      TNS for Linux: Version - Production
      NLSRTL Version - Production

      Sample data:
      create table t1( o_id  number(3)
                     , oname varchar2(20)
                     , typ  varchar2(3)
                     , prnt number(3)
      insert into t1 values(118,   'Pension Admin',  'PRC',  91);
      insert into t1 values(91,    'Retirement',     'LOB', 218);
      insert into t1 values(218,   'Benefits',       'SEG',  81);
      insert into t1 values(119,   'Plan Mgmt',      'PRC',  91);
      insert into t1 values(142,   'RCS Software',   'PRC',  93);
      insert into t1 values(93,    'Risk Software',  'LOB', 221);
      insert into t1 values(221,   'Risk Services',  'SEG',  81);
      I can't get past:
      select level lvl, org.*
        from t1 org
        start with typ = 'PRC'
        connect by  o_id =  prior prnt;
      I've tried this, but of course it returns only one row:
      select *
        from (
               select lvl, oname
                 from (  
                        select level lvl, t1.*
                          from t1 
                         start with typ = 'PRC'
                       connect by  o_id =  prior prnt
      pivot ( min(oname)  for lvl in (1  prc, 2 lob, 3 seg) )
      What am I missing, any help would be appreciated.
        • 1. Re: Pivot help on an heirarchy query
          Frank Kulash

          Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful!

          Use CONNECT_BY_ROOT to remember the starting point that led to each row of output, which is what identifies which row of output each row of the CONNECT BY results corresponds to.

          Sorry, I'm not at an Oracle 11 system now. This works in Oracle 10 (and up):
          WITH     connect_by_results     AS
               SELECT     LEVEL               AS lvl
               ,      oname
                 ,     CONNECT_BY_ROOT     o_id     AS starting_o_id
               FROM     t1
                 START WITH     typ      = 'PRC'
               CONNECT BY     o_id      =  PRIOR prnt
          SELECT       starting_o_id
          ,       MIN (CASE WHEN lvl = 1 THEN oname END)     AS prc
          ,       MIN (CASE WHEN lvl = 2 THEN oname END)     AS lob
          ,       MIN (CASE WHEN lvl = 3 THEN oname END)     AS seg
          FROM       connect_by_results
          GROUP BY  starting_o_id
          ORDER BY  starting_o_id
          You can adapt the main query to use SELECT ... PIVOT.

          Do you really need separate columns for the different levels? If not, you could do something like this:
          SELECT     CONNECT_BY_ROOT     o_id     AS starting_o_id
          ,      REPLACE ( SYS_CONNECT_BY_PATH ( RPAD (oname, 15)
                                    , '`'
                    , '`'
                    )     AS prc_lob_seg
          FROM     t1
          WHERE     CONNECT_BY_ISLEAF     = 1
          START WITH     typ      = 'PRC'
          CONNECT BY     o_id      =  PRIOR prnt
          ORDER BY     starting_o_id
          ------------- --------------------------------------------------
                    118 Pension Admin  Retirement     Benefits
                    119 Plan Mgmt      Retirement     Benefits
                    142 RCS Software   Risk Software  Risk Services
          Not only is this simpler, it works for any number of levels.

          Edited by: Frank Kulash on Jan 16, 2012 4:25 PM
          Added SYS_CONNECT_BY_PATH alternative.
          1 person found this helpful
          • 2. Re: Pivot help on an heirarchy query
            Solomon Yakobson
            with org as (
                         select  connect_by_root o_id o_id,
                           from  t1
                           start with typ = 'PRC'
                           connect by  o_id =  prior prnt
            select  *
              from  org
                    max(oname) for typ in('PRC' PRC,'LOB' LOG,'SEG' SEG)
                  O_ID     PRC                      LOG                      SEG
            ----------     --------------------     --------------------     --------------------
                   119     Plan Mgmt                Retirement               Benefits
                   142     RCS Software             Risk Software            Risk Services
                   118     Pension Admin            Retirement               Benefits
            • 3. Re: Pivot help on an heirarchy query
              That's it!
              Thanks to both!