Boyd Timmerman Boyd Timmerman - 9 months ago 75
SQL Question

Pivot multiple colums Oracle SQL

I have a table called "Books"

ID | lan_id | main_title | part
---------------------------------
1 | 1 | Quick guide | 1
2 | 1 | Quick guide | 4
---------------------------------


what I want to achieve is:

--------------------------------------------
ID | 1 | 2
Lan_id | 1 | 1
main_tile | Quick guide | Quick guide
part | 1 | 4
--------------------------------------------


I think I need a pivot, but I have no idea how to do this. Can you help me?

MT0 MT0
Answer Source

Use UNPIVOT then PIVOT:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( ID, lan_id, main_title, part ) AS
SELECT 1, 1, 'Quick guide', 1 FROM DUAL UNION ALL
SELECT 2, 1, 'Quick guide', 4 FROM DUAL;

Query 1:

SELECT *
FROM   ( SELECT ROWNUM AS rn,
                TO_CHAR( id ) AS id,
                TO_CHAR( lan_id ) AS lan_id,
                main_title,
                TO_CHAR( part ) AS part
         FROM   table_name )
UNPIVOT ( value FOR key IN (
  id,
  lan_id,
  main_title,
  part
) )
PIVOT ( MAX( value ) FOR rn IN (
  1 AS "1",
  2 AS "2"
) )
ORDER BY key

Results:

|        KEY |           1 |           2 |
|------------|-------------|-------------|
|         ID |           1 |           2 |
|     LAN_ID |           1 |           1 |
| MAIN_TITLE | Quick guide | Quick guide |
|       PART |           1 |           4 |
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download