Pivot multiple columns Oracle SQL

Question

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.

MT0 · Accepted Answer · 2017-10-03 08:45:44Z

0

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 |

answered Oct 3, 2017 at 8:45

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kaushik Nayak Over a year ago

I did not copy your answer although it appears that way...:)

Kaushik Nayak · Accepted Answer · 2017-10-03 09:01:51Z

You can use both UNPIVOT and PIVOT as follows.

WITH Books (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)
SELECT *
  FROM (SELECT *
          FROM (SELECT TO_CHAR (id) id,
                       TO_CHAR (lan_id) lan_id,
                       main_title,
                       TO_CHAR (part) part,
                       TO_CHAR (ROWNUM) rn
                  FROM Books) UNPIVOT (columns
                              FOR header
                              IN (Id, lan_id, main_title, part))) PIVOT (MAX (
                                                                            columns)
                                                                     FOR rn
                                                                     IN (1 AS row1,
                                                                        2 AS row2));

Output:

Header             row1 row2
-----              ---- ----
ID                 1    2
LAN_ID             1    1
MAIN_TITLE  Quick guide Quick guide
PAIR               1    4

Collectives™ on Stack Overflow

Pivot multiple columns Oracle SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related