Pivot statement in Oracle
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 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
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