Thursday, 23 February 2012

Folding table in Oracle using unpivot function

In this post, I am unsing unpivot function to change the arity and cardinality in the result. mXn table is used to get pXq result such that product(m,n) = product(p,q)
Our sample table has the 6 columns and 1 row. We are using unpivot function to get the same data grouped into 3 columns and 2 rows.
CREATE TABLE folding_table AS SELECT     DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 2.9)),
                   1, 'Earth',
                   2, 'Mars'
                  ) AS planet_a,
           DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 2.9)),
                   1, 'Cow',
                   2, 'Tiger'
                  ) AS animal_a,
           DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 2.9)),
                   1, 'Blue',
                   2, 'Green'
                  ) AS color_a,
           DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 2.9)),
                   1, 'Earth',
                   2, 'Mars'
                  ) AS planet_b,
           DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 2.9)),
                   1, 'Cow',
                   2, 'Tiger'
                  ) AS animal_b,
           DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 2.9)),
                   1, 'Blue',
                   2, 'Green'
                  ) AS color_b
      FROM DUAL
CONNECT BY ROWNUM <= 1
This gives the following table
image
Now we use the following query to fold it
select planet,animal,color from folding_table
unpivot ((planet,
         animal,
         color)
for Groups IN ((planet_a,
         animal_a,
         color_a) as 'Group A',
         (planet_b,
         animal_b,
         color_b) as 'Group B'))
image

1 comment:

Jennifer N said...

Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com