Oracle Database - (Pivot|Unpivot) Statement

> Database > Oracle Database

1 - About

Pivot statement in Oracle

3 - Example

3.1 - Pivot

The column from the first select will be created with as prefix the value of the column in the for statement of the pivot statement.

SELECT   *
  FROM
    ( SELECT default_tablespace, TEMPORARY_TABLESPACE, username FROM dba_users 
    ) pivot 
    ( MAX ( default_tablespace ) AS default_tablespace_max, -- if only one pivot is the alias not needed
      MAX ( temporary_TABLESPACE ) AS temporary_TABLESPACE_max 
      FOR username IN (
           'OE' AS "OE",
           'SH' AS "SH"
           ) ) ; 
'OE'_DEFAULT_TABLESPACE_MAX	'OE'_TEMPORARY_TABLESPACE_MAX	'SH'_DEFAULT_TABLESPACE_MAX  'SH'_TEMPORARY_TABLESPACE_MAX
EXAMPLE	                        TEMP                            EXAMPLE                      TEMP
Advertising

3.2 - Unpivot

Unpivot can be used to see all column value in one column. You define the new column name in the unpivot block (ie column_Name_Value) and the list of column that must be unpivoted.

SELECT column_name, column_name_value FROM
(SELECT 
          "USERNAME", 
          "DEFAULT_TABLESPACE", 
          "TEMPORARY_TABLESPACE", 
          TO_CHAR("USER_ID") AS USER_ID, 
          NVL("PASSWORD",'null') AS PASSWORD , 
          "ACCOUNT_STATUS", 
          NVL(TO_CHAR("LOCK_DATE"),'null') AS LOCK_DATE,
          NVL(TO_CHAR("EXPIRY_DATE"), 'null') AS EXPIRY_DATE, 
          TO_CHAR("CREATED") AS CREATED, 
          "PROFILE"
  FROM dba_users WHERE username IN ('SH') )
unpivot
(
    column_Name_Value -- The name of the new column that will get the unpivoted data
    FOR column_Name IN ( -- The column that you want to unpivot. They must have the same data type. They  must be not null to show up.
          "USERNAME", 
          "DEFAULT_TABLESPACE",
          "TEMPORARY_TABLESPACE",
          "USER_ID",
          "PASSWORD",
          "ACCOUNT_STATUS",
          "LOCK_DATE", 
          "EXPIRY_DATE", 
          "CREATED", 
          "PROFILE")
)
ORDER BY column_name
;
COLUMN COLUMN_NAME_VALUE FORMAT A30
COLUMN COLUMN_NAME FORMAT A30
  • Result:
COLUMN_NAME                    COLUMN_NAME_VALUE
------------------------------ ----------------------
ACCOUNT_STATUS                 LOCKED
CREATED                        07-07-14
DEFAULT_TABLESPACE             EXAMPLE
EXPIRY_DATE                    null
LOCK_DATE                      30-07-14
PASSWORD                       null
PROFILE                        DEFAULT
TEMPORARY_TABLESPACE           TEMP
USER_ID                        107
USERNAME                       SH

4 - Documentation / Reference

db/oracle/pivot.txt · Last modified: 2017/09/13 21:22 by gerardnico