Another Simple Pivot question - rotate text column to row

Mr Neil

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   

ID	RECORD_NUMBER	ITEMS	QTY
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_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

Any help appreciated.

NT

Frank Kulash

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

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

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

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

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

Thanks all for your effort.

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

NT

Solomon Yakobson

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

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