Oracle Database - (Pivot|Unpivot) Statement

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
;

• SQL PLUS statement for formatting

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