Search code examples
sqloracle-databaseunpivot

Oracle transpose a simple table

I have a simple table from a select query that looks like this

CATEGORY   | EQUAL | LESS  | GREATER
VALUE      |  60   |  100  |    20

I want to be able to transpose it so it looks like this

CATEGORY | VALUE
EQUAL    | 60
LESS     | 100
GREATER  | 20

I tried using the pivot function in oracle but I can't seem to get it to work. I've tried looking all over online but I can't find anything that will help me. Any help is much appreciated thank you!

Solution

  • You can use union all:

    select 'EQUAL' as category, equal as value from t union all
select 'LESS' as category, less from t union all
select 'GREATER' as category, greater from t;

    If you had a large table, you might want to try some other method (such as a lateral join in Oracle 12c). But for a small table, this is fine.