5 Replies Latest reply: Nov 13, 2012 8:24 AM by Nicosa-Oracle RSS

    display columns as rows from non-unique key table

    973493
      Hi OTN/Users, I hope you can assist me

      Given a table:

      create table t (a varchar2(30), b int, c date );

      with this data within:

      insert into t values ( a1, 40, to_date( '01-Dec-2012'));
      insert into t values ( a1, 50, to_date( '01-Dec-2012'));
      insert into t values ( a1, 60, to_date( '01-Dec-2012'));
      insert into t values ( b1, 10, to_date( '01-Dec-2012'));
      insert into t values ( b1, 20, to_date( '01-Dec-2012'));
      insert into t values ( b1, 30, to_date( '01-Dec-2012'));
      insert into t values ( c1, 60, to_date( '01-Dec-2012'));
      insert into t values ( c1, 70, to_date( '01-Dec-2012'));
      insert into t values ( c1, 80, to_date( '01-Dec-2012'));

      - I want to output the columns for each of 'a' as a single row e.g:

      a1 40 50 60 01-Dec-2012
      b1 10 20 30 01-Dec-2012
      ...

      I've almost got it right, but the 'a' col repeats 4 times for each row of output:

      a1 40
      a1 50
      a1 60
      a1 01-Dec-2012

      -I want to supress repeat output of the first column 'a' but display the rest in a straight line.

      I've tried various things (Pivot, Rollup etc), but the fact i'm keying on a table with non unique rows has complicated things perhaps.
      Any help would be much appreciated
        • 1. Re: display columns as rows from non-unique key table
          Nicosa-Oracle
          Hi,

          Pre-11g this is how you would do it :
          [11.2] Pri @ Bepripd1 > !cat t.sql
          with t(a,b,c) as (
               select  'a1', 40, to_date( '01-Dec-2012') from dual union all
               select  'a1', 50, to_date( '01-Dec-2012') from dual union all
               select  'a1', 60, to_date( '01-Dec-2012') from dual union all
               select  'b1', 10, to_date( '01-Dec-2012') from dual union all
               select  'b1', 20, to_date( '01-Dec-2012') from dual union all
               select  'b1', 30, to_date( '01-Dec-2012') from dual union all
               select  'c1', 60, to_date( '01-Dec-2012') from dual union all
               select  'c1', 70, to_date( '01-Dec-2012') from dual union all
               select  'c1', 80, to_date( '01-Dec-2012') from dual
          )
          ------ end of sample data ------
          select
               a
               ,max(decode(n,1,b,null)) q1
               ,max(decode(n,2,b,null)) q2
               ,max(decode(n,3,b,null)) q3
               ,c
          from (
               select a, b, c, row_number() over (partition by a order by b) n
               from t
          )
          group by a,c
          order by a,c
          /
          
          [11.2] Pri @ Bepripd1 > @t
          
          A          Q1         Q2         Q3 C
          -- ---------- ---------- ---------- -------------------
          a1         40         50         60 01/12/2012 00:00:00
          b1         10         20         30 01/12/2012 00:00:00
          c1         60         70         80 01/12/2012 00:00:00
          ------
          From 11g onward, you would :
          [11.2] Pri @ Bepripd1 > !cat t.sql
          with t(a,b,c) as (
               select  'a1', 40, to_date( '01-Dec-2012') from dual union all
               select  'a1', 50, to_date( '01-Dec-2012') from dual union all
               select  'a1', 60, to_date( '01-Dec-2012') from dual union all
               select  'b1', 10, to_date( '01-Dec-2012') from dual union all
               select  'b1', 20, to_date( '01-Dec-2012') from dual union all
               select  'b1', 30, to_date( '01-Dec-2012') from dual union all
               select  'c1', 60, to_date( '01-Dec-2012') from dual union all
               select  'c1', 70, to_date( '01-Dec-2012') from dual union all
               select  'c1', 80, to_date( '01-Dec-2012') from dual
          )
          ------ end of sample data ------
          select a,q1,q2,q3,c
          from (
               select a, b, c, row_number() over (partition by a order by b) n
               from t
          )
          pivot (
               max(b)
               for n in (
                    1 as q1
                    ,2 as q2
                    ,3 as q3
               )
          )
          order by a,c
          /
          
          [11.2] Pri @ Bepripd1 > @t
          
          A          Q1         Q2         Q3 C
          -- ---------- ---------- ---------- -------------------
          a1         40         50         60 01/12/2012 00:00:00
          b1         10         20         30 01/12/2012 00:00:00
          c1         60         70         80 01/12/2012 00:00:00
          Edited by: Nicosa on Nov 9, 2012 2:42 PM
          • 2. Re: display columns as rows from non-unique key table
            Kim Berg Hansen
            Even [url http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5658416800346527104]Tom agrees with Nicosa ;-)
            • 3. Re: display columns as rows from non-unique key table
              Nicosa-Oracle
              Hi Kim,
              Kim Berg Hansen wrote:
              Even [url http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5658416800346527104]Tom agrees with Nicosa ;-)
              Very honored that Tom decided to use <b><u>my</u></b> solution. ;-) ;-) ;-)
              But I guess that means the OP might not comme back here to mark the thread as answered.
              • 4. Re: display columns as rows from non-unique key table
                973493
                Thanks Nicosa, I'm not sure who beat who here, it but I appreciate the help all the same - and solution works.
                regards
                • 5. Re: display columns as rows from non-unique key table
                  Nicosa-Oracle
                  970490 wrote:
                  Thanks Nicosa, I'm not sure who beat who here, it but I appreciate the help all the same - and solution works.
                  regards
                  You're welcome.

                  The "my solution" part was a joke, as I actually learned the pré-11g pivot from Tom's famous site : [url asktom.oracle.com]asktom.oracle.com
                  I owe him a lot when it comes to Oracle !