Oracle Database - (Pivot|Unpivot) Statement

About

Pivot statement in Oracle

Example

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

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

Documentation / Reference


Powered by ComboStrap