Forum Stats

  • 3,851,567 Users
  • 2,264,000 Discussions
  • 7,904,781 Comments

Discussions

Another Simple Pivot question - rotate text column to row

Mr Neil
Mr Neil Member Posts: 113 Blue Ribbon
edited Jun 21, 2016 9:26AM in SQL & PL/SQL

Hi guys,

Stuck again.  Just can't get my head around these pivots. Oracle 11g.

I need to pivot 3 rows up to 3 columns with a varying number of row items. It's the varying amount of column-to-be data that I'm having trouble with

Source   

IDRECORD_NUMBERITEMSQTY
1100widget_69100
2101widget_70150
3101widget_71200
4102widget_72250
5102widget_73300
6102widget_74350

The Target   

RECORD_NUMBERITEM_1QTY_1ITEM_2QTY_2ITEM_3QTY_3
100widget_69100
101widget_70150widget_71200
102widget_72250widget_73300widget_74350

Any help appreciated.

NT

Tagged:
Mr Neil

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,475 Red Diamond
    edited Jun 20, 2016 7:02PM

    Hi,

    Mr Neil wrote:
    
    Hi guys,
    
    Stuck again.  Just can't get my head around these pivots. Oracle 11g.
    
    I need to pivot 3 rows up to 3 columns with a varying number of row items. It's the varying amount of column-to-be data that I'm having trouble with
    
    Source  
    
    
    IDRECORD_NUMBERITEMSQTY
    
    
    
    1
    100
    widget_69
    100
    
    
    2
    101
    widget_70
    150
    
    
    3
    101
    widget_71
    200
    
    
    4
    102
    widget_72
    250
    
    
    5
    102
    widget_73
    300
    
    
    6
    102
    widget_74
    350
    
    
    
    
    
    The Target  
    
    
    RECORD_NUMBERITEM_1QTY_1ITEM_2QTY_2ITEM_3QTY_3
    
    
    
    100
    widget_69
    100
    
    
    
    
    
    101
    widget_70
    150
    widget_71
    200
    
    
    
    102
    widget_72
    250
    widget_73
    300
    widget_74
    350
    
    
    
    
    Any help appreciated.
    
    NT
    
    

    The result set needs to have a fixed number of columns, hard-coded when the query is compiled.

    You can write a query that will always produce the 7 columns you displayed above, even if no record_number ever occurs 3 times.  Some columns at the end may be NULL, but no error will occur.

    Since you have Oracle 11, you should use the SELECT ... PIVOT feature.  You can pivot pairs of columns, e.g. items and qty together.

    Use the analytic ROW_NUMBER function to assign numbers 1, 2 or 3 to each row, depending on whether it will popultae the columns  ending in _1, _2 or _3.  (Why is 'widget_72 in the item_1 column, rather than item_2 or item_3?  Remember, rows in a table have no built-in order, so if you use words like "first"  or "next" in your explanation, then define what they mean.)

    If you'd care to post CREATE TABLE and INSERT statements for the sample data, then I could show you exactly how to do it.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,720 Red Diamond
    edited Jun 20, 2016 7:06PM

    It might be simpler and more efficient using analytics:

    with t as (

               select  row_number() over(partition by record_number order by id) rn,

                       record_number,

                       items item_1,

                       qty qty_1,

                       lead(items) over(partition by record_number order by id) item_2,

                       lead(qty) over(partition by record_number order by id) qty_2,

                       lead(items,2) over(partition by record_number order by id) item_3,

                       lead(qty,2) over(partition by record_number order by id) qty_3

                 from  sample

              )

    select  record_number,

            item_1,

            qty_1,

            item_2,

            qty_2,

            item_3,

            qty_3

      from  t

      where rn = 1

    /

    RECORD_NUMBER ITEM_1         QTY_1 ITEM_2         QTY_2 ITEM_3         QTY_3

    ------------- --------- ---------- --------- ---------- --------- ----------

              100 widget_69        100

              101 widget_70        150 widget_71        200

              102 widget_72        250 widget_73        300 widget_74        350

    SQL>

    SY.

    Mr Neil
  • Mr Neil
    Mr Neil Member Posts: 113 Blue Ribbon
    edited Jun 20, 2016 7:47PM
    Frank Kulash wrote:
    
    Hi,
    
    Mr Neil wrote:
    
    Hi guys,
    
    Stuck again.  Just can't get my head around these pivots. Oracle 11g.
    
    I need to pivot 3 rows up to 3 columns with a varying number of row items. It's the varying amount of column-to-be data that I'm having trouble with
    
    Source 
    
    
    IDRECORD_NUMBERITEMSQTY
    
    
    
    1
    100
    widget_69
    100
    
    
    2
    101
    widget_70
    150
    
    
    3
    101
    widget_71
    200
    
    
    4
    102
    widget_72
    250
    
    
    5
    102
    widget_73
    300
    
    
    6
    102
    widget_74
    350
    
    
    
    
    
    The Target 
    
    
    RECORD_NUMBERITEM_1QTY_1ITEM_2QTY_2ITEM_3QTY_3
    
    
    
    100
    widget_69
    100
    
    
    
    
    
    101
    widget_70
    150
    widget_71
    200
    
    
    
    102
    widget_72
    250
    widget_73
    300
    widget_74
    350
    
    
    
    
    Any help appreciated.
    
    NT
    
    The result set needs to have a fixed number of columns, hard-coded when the query is compiled.
    You can write a query that will always produce the 7 columns you displayed above, even if no record_number ever occurs 3 times.  Some columns at the end may be NULL, but no error will occur.
    Since you have Oracle 11, you should use the SELECT ... PIVOT feature.  You can pivot pairs of columns, e.g. items and qty together.
    Use the analytic ROW_NUMBER function to assign numbers 1, 2 or 3 to each row, depending on whether it will popultae the columns  ending in _1, _2 or _3.  (Why is 'widget_72 in the item_1 column, rather than item_2 or item_3?  Remember, rows in a table have no built-in order, so if you use words like "first"  or "next" in your explanation, then define what they mean.)
    
    If you'd care to post CREATE TABLE and INSERT statements for the sample data, then I could show you exactly how to do it.
    

    Much Thanks.

    Yes, I think I will need an example to understand.

    The order of the widgets are not important, but I see now that I'll have to give them order to achieve the reasult Still, I'm not going to be able to nut this out. Code provided.

    CREATE TABLE  "TEST_TABLE"

       (    "ID" NUMBER,

        "RECORD_NUMBER" NUMBER,

        "ITEMS" VARCHAR2(30),

        "QTY" NUMBER,

         CONSTRAINT "TEST_TABLE_PK" PRIMARY KEY ("ID") ENABLE

       )

    Insert all

    into TEST_TABLE (ID,RECORD_NUMBER,ITEMS,QTY)

    Values (1,100,'widget_69',100)

    into TEST_TABLE (ID,RECORD_NUMBER,ITEMS,QTY)

    Values (2,101,'widget_70',150)

    into TEST_TABLE (ID,RECORD_NUMBER,ITEMS,QTY)

    Values (3,101,'widget_71',200)

    into TEST_TABLE (ID,RECORD_NUMBER,ITEMS,QTY)

    Values (4,102,'widget_72',250)

    into TEST_TABLE (ID,RECORD_NUMBER,ITEMS,QTY)

    Values (5,102,'widget_73',300)

    into TEST_TABLE (ID,RECORD_NUMBER,ITEMS,QTY)

    Values (6,102,'widget_74',350)

    Select * from Dual;

    NT

  • Mr Neil
    Mr Neil Member Posts: 113 Blue Ribbon
    edited Jun 20, 2016 8:42PM
    Solomon Yakobson wrote:
    
    It might be simpler and more efficient using analytics:
    
    with t as (
               select  row_number() over(partition by record_number order by id) rn,
                       record_number,
                       items item_1,
                       qty qty_1,
                       lead(items) over(partition by record_number order by id) item_2,
                       lead(qty) over(partition by record_number order by id) qty_2,
                       lead(items,2) over(partition by record_number order by id) item_3,
                       lead(qty,2) over(partition by record_number order by id) qty_3
                 from  sample
              )
    select  record_number,
            item_1,
            qty_1,
            item_2,
            qty_2,
            item_3,
            qty_3
      from  t
      where rn = 1
    /
    
    RECORD_NUMBER ITEM_1         QTY_1 ITEM_2         QTY_2 ITEM_3         QTY_3
    ------------- --------- ---------- --------- ---------- --------- ----------
              100 widget_69        100
              101 widget_70        150 widget_71        200
              102 widget_72        250 widget_73        300 widget_74        350
    
    SQL>
    
    SY.
    

    Awesome.

    Thanks for that. I'll see if I can digest this.

    NT

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,475 Red Diamond
    edited Jun 20, 2016 8:44PM

    Hi,

    Here's one way:

    WITH    got_col_num    AS
    (
        SELECT  record_number, items, qty
        ,      ROW_NUMBER () OVER ( PARTITION BY  record_number
                                    ORDER BY      id
                                  )  AS c_num
        FROM    test_table
    )
    SELECT    *
    FROM      got_col_num
    PIVOT     ( MIN (items)  AS item
              , MIN (qty)    AS qty
              FOR  c_num  IN  ( 1  AS c1
                              , 2  AS c2
                              , 3  AS c3
                              )
              )
    ORDER BY  record_number
    ;
    
    

    Output:

    RECORD_NUMBER C1_ITEM   C1_QTY C2_ITEM   C2_QTY C3_ITEM   C3_QTY
    ------------- --------- ------ --------- ------ --------- ------
              100 widget_69    100
              101 widget_70    150 widget_71    200
              102 widget_72    250 widget_73    300 widget_74    350
    
    

    If you want column names like item_1 and qty_1, then explicitly name the columns instead of using "SELECT *" in the main query.

    Mr Neil
  • Mr Neil
    Mr Neil Member Posts: 113 Blue Ribbon
    edited Jun 20, 2016 9:27PM

    Thanks all for your effort.

    I'm slightly more familiar with the pivot. Will have to digest both.

    NT

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,720 Red Diamond
    edited Jun 21, 2016 8:25AM
    Mr Neil wrote:
    
    I'm slightly more familiar with the pivot. Will have to digest both.
    
    

    Compare plans:

    SQL> explain plan for

      2  with t as (

      3            select  row_number() over(partition by record_number order by id) rn,

      4                    record_number,

      5                    items item_1,

      6                    qty qty_1,

      7                    lead(items) over(partition by record_number order by id) item_2,

      8                    lead(qty) over(partition by record_number order by id) qty_2,

      9                    lead(items,2) over(partition by record_number order by id) item_3,

    10                    lead(qty,2) over(partition by record_number order by id) qty_3

    11              from  test_table

    12            )

    13  select  record_number,

    14          item_1,

    15          qty_1,

    16          item_2,

    17          qty_2,

    18          item_3,

    19          qty_3

    20    from  t

    21    where rn = 1

    22    order by record_number

    23  /

    Explained.

    SQL> select  *

      2    from  table(dbms_xplan.display)

      3  /

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------------------------------------------

    Plan hash value: 2151000099

    -----------------------------------------------------------------------------------

    | Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)|  Time    |

    -----------------------------------------------------------------------------------

    |  0  | SELECT STATEMENT     |            |     6 |   696 |     4  (25)| 00:00:01 |

    |*  1 |  VIEW                |            |     6 |   696 |     4  (25)| 00:00:01 |

    |  2  |  WINDOW SORT         |            |     6 |   336 |     4  (25)| 00:00:01 |

    |  3  |    TABLE ACCESS FULL | TEST_TABLE |     6 |   336 |     3  (0) | 00:00:01 |

    -----------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      1 - filter("RN"=1)

    Note

    -----

      - dynamic statistics used: dynamic sampling (level=2)

    19 rows selected.

    SQL> explain plan for

      2  WITH    got_col_num    AS

      3  (

      4      SELECT  record_number, items, qty

      5      ,      ROW_NUMBER () OVER ( PARTITION BY  record_number

      6                                  ORDER BY      id

      7                                )  AS c_num

      8      FROM    test_table

      9  )

    10  SELECT    *

    11  FROM      got_col_num

    12  PIVOT    ( MIN (items)  AS item

    13            , MIN (qty)    AS qty

    14            FOR  c_num  IN  ( 1  AS c1

    15                            , 2  AS c2

    16                            , 3  AS c3

    17                            )

    18            )

    19  ORDER BY  record_number

    20  ;

    Explained.

    SQL> select  *

      2    from  table(dbms_xplan.display)

      3  /

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------------------------------------------

    Plan hash value: 2282687754

    -----------------------------------------------------------------------------------------

    | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

    -----------------------------------------------------------------------------------------

    |  0 | SELECT STATEMENT            |            |     6 |   336 |    4  (25) | 00:00:01 |

    |  1 |  SORT GROUP BY NOSORT PIVOT |            |     6 |   336 |    4  (25) | 00:00:01 |

    |  2 |  VIEW                       |            |     6 |   336 |    4  (25) | 00:00:01 |

    |  3 |    WINDOW SORT              |            |     6 |   336 |    4  (25) | 00:00:01 |

    |  4 |    TABLE ACCESS FULL        | TEST_TABLE |     6 |   336 |    3   (0) | 00:00:01 |

    -----------------------------------------------------------------------------------------

    Note

    -----

      - dynamic statistics used: dynamic sampling (level=2)

    15 rows selected.

    SQL>

    SY.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,305 Red Diamond
    edited Jun 21, 2016 9:26AM

    Let's not forget old style (10g compatible) pivoting...

    SQL> ed
    Wrote file afiedt.buf   1  with test_table(id, record_number, items, qty) as (
      2    select 1,100,'widget_69',100 from dual union all
      3    select 2,101,'widget_70',150 from dual union all
      4    select 3,101,'widget_71',200 from dual union all
      5    select 4,102,'widget_72',250 from dual union all
      6    select 5,102,'widget_73',300 from dual union all
      7    select 6,102,'widget_74',350 from dual
      8    )
      9  --
    10  -- end of test data
    11  --
    12  select record_number
    13        ,max(case when rn = 1 then items else null end) as item_1
    14        ,max(case when rn = 1 then qty else null end) as qty_1
    15        ,max(case when rn = 2 then items else null end) as item_2
    16        ,max(case when rn = 2 then qty else null end) as qty_2
    17        ,max(case when rn = 3 then items else null end) as item_3
    18        ,max(case when rn = 3 then qty else null end) as qty_3
    19  from  (select record_number, items, qty
    20               ,row_number() over (partition by record_number order by items) as rn
    21         from   test_table
    22        )
    23  group by record_number
    24* order by record_number
    25  / RECORD_NUMBER ITEM_1         QTY_1 ITEM_2         QTY_2 ITEM_3         QTY_3
    ------------- --------- ---------- --------- ---------- --------- ----------
              100 widget_69        100
              101 widget_70        150 widget_71        200
              102 widget_72        250 widget_73        300 widget_74        350
This discussion has been closed.