DEMO: HOW TO TRANSPOSE DATA FROM COLUMNS TO ROWS IN AN ORACLE DATABASE
Demos, Queries
The Oracle UNPIVOT clause allows you to transpose columns to rows. Here is a simple example for a reference without any explanation.
QUERY EXAMPLE
I want to transpose the following data from columns to rows
Single Row
-- There is a single row in the result set
COL COL1 FOR A4
COL COL2 FOR A4
COL COL3 FOR A4
SELECT 'A' COL1, 'B' COL2, 'C' COL3 FROM DUAL;
COL1 COL2 COL3
---- ---- ----
A B C
-- Transposing columns to rows
COL COLUMNS FOR A7
COL VALUE FOR A5
WITH DATA AS (
SELECT 'A' COL1, 'B' COL2, 'C' COL3 FROM DUAL
)
SELECT * FROM DATA
UNPIVOT (
VALUE FOR COLUMNS IN (COL1,COL2,COL3)
);
COLUMNS VALUE
------- -----
COL1 A
COL2 B
COL3 C
Multiple Row
-- There are many rows in the result set
COL COL1 FOR A4
COL COL2 FOR A4
COL COL3 FOR A4
SELECT 'A' COL1, 'B' COL2, 'C' COL3 FROM DUAL
UNION ALL
SELECT 'D' COL1, 'E' COL2, 'F' COL3 FROM DUAL
UNION ALL
SELECT 'G' COL1, 'H' COL2, 'I' COL3 FROM DUAL;
COL1 COL2 COL3
---- ---- ----
A B C
D E F
G H I
-- Transposing columns to rows
COL COLUMNS FOR A7
COL VALUE FOR A5
WITH DATA AS (
SELECT 'A' COL1, 'B' COL2, 'C' COL3 FROM DUAL
UNION ALL
SELECT 'D' COL1, 'E' COL2, 'F' COL3 FROM DUAL
UNION ALL
SELECT 'G' COL1, 'H' COL2, 'I' COL3 FROM DUAL
)
SELECT * FROM DATA
UNPIVOT (
VALUE FOR COLUMNS IN (COL1,COL2,COL3)
);
COLUMNS VALUE
------- -----
COL1 A
COL2 B
COL3 C
COL1 D
COL2 E
COL3 F
COL1 G
COL2 H
COL3 I
Written At
05 NOV 202123:00
OEL Release
7.9 x64
Database Release
19.12.0.0.0
Share
- Accounts
- Auditing
- AWR
- Bash Scripts
- Datapump
- Default Category
- Demos
- Directory Objects
- Environment Variables
- Initialization Parameters
- Iptables
- Java Program
- Memory Usage
- Metadata API
- Networker
- NLS Settings
- Optimizer Statistics
- ORA-00942
- ORA-01031
- ORA-01720
- ORA-28001
- ORA-31671
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Postfix
- Privilegies
- Processes
- Queries
- Red Hat Enterprise Linux
- Redo Logs
- Session Tracing
- Sessions
- SQL Trace
- SQLPlus
- Statspack
- Tablespaces
- UTL_FILE
- UTL_FILE_DIR
- Wait Events
- Yum