CODEWITHSUNDEEP
oracle11gunpivot
AndreA
AndreA

Reputation: 789

Converting column to rows in Oracle 11g

I have a table like this:

+----+----------+----------+----------+-----------+----------+----------+
| ID | AR_SCORE | ER_SCORE | FS_SCORE | CPF_SCORE | IF_SCORE | IS_SCORE |
+----+----------+----------+----------+-----------+----------+----------+
|  1 |       25 |       35 |       45 |        55 |       65 |       75 |
|  2 |       95 |       85 |       75 |        65 |       55 |       45 |
+----+----------+----------+----------+-----------+----------+----------+

And I need to extract this:

+----+----------+-------+
| ID |  SCORE   | VALUE |
+----+----------+-------+
|  1 | AR_SCORE |    25 |
|  1 | ER_SCORE |    35 |
|  2 | AR_SCORE |    95 |
+----+----------+-------+

I read many questions about how to use pivoting in oracle but I could not make it work.

Upvotes: 2

Views: 158

Answers (1)

Taryn
Taryn

Reputation: 247720

The conversion from columns into rows is actually an UNPIVOT. Since you are using Oracle 11g there are a few ways that you can get the result.

The first way would be using a combination of SELECT yourcolumn FROM...UNION ALL:

select ID, 'AR_SCORE' as Score, AR_SCORE as value
from yourtable
union all
select ID, 'ER_SCORE' as Score, ER_SCORE as value
from yourtable
union all
select ID, 'FS_SCORE' as Score, FS_SCORE as value
from yourtable
union all
select ID, 'IF_SCORE' as Score, IF_SCORE as value
from yourtable
union all
select ID, 'IS_SCORE' as Score, IS_SCORE as value
from yourtable
order by id

See Demo. Using UNION ALL was how you needed to unpivot data prior to Oracle 11g, but starting in that version the UNPIVOT function was implemented. This will get you the same result with fewer lines of code:

select ID, Score, value
from yourtable
unpivot
(
  value
  for Score in (AR_SCORE, ER_SCORE, FS_SCORE, IF_SCORE, IS_SCORE)
) un
order by id

See Demo. Both will give a result:

| ID |    SCORE | VALUE |
|----|----------|-------|
|  1 | AR_SCORE |    25 |
|  1 | FS_SCORE |    45 |
|  1 | IS_SCORE |    75 |
|  1 | IF_SCORE |    65 |
|  1 | ER_SCORE |    35 |
|  2 | FS_SCORE |    75 |
|  2 | IS_SCORE |    45 |
|  2 | ER_SCORE |    85 |
|  2 | IF_SCORE |    55 |
|  2 | AR_SCORE |    95 |

Upvotes: 3

Related Questions