CODEWITHSUNDEEP
sqloracle-databasepivot
Jostein Topland
Jostein Topland

Reputation: 1020

Oracle SQL: Transpose / rotate a table result having one row and many columns

I'm looking for a way to transpose or rotate a table in Oracle SQL. For this case there is only one row in the SELECT, but multiple columns.

Example:

SELECT
  id AS "Id",
  name AS "Name",
  some_value AS "Favorite color"
FROM
  table
WHERE
  id = 5;

Result:

id | name | some_value
--- ------ -----------
5   John    Orange

What I would like to see is:

Id             | 5
Name           | John
Favorite color | Orange

I'm aware of PIVOT, but I'm struggling to see a simple code with this case.

Upvotes: 2

Views: 7324

Answers (3)

Jostein Topland
Jostein Topland

Reputation: 1020

Frank Ockenfuss gave the answer I was looking for. Thanks, Frank!

However, a minor change makes changing the column names a bit more easier:

SELECT * FROM (
  SELECT
    TO_CHAR(id) AS id,
    TO_CHAR(name) AS name,
    TO_CHAR(some_value) AS fav_color
  FROM my_table
  WHERE id = 5
) UNPIVOT(value FOR key IN(
  id AS 'Id',
  name AS 'Name',
  fav_color AS 'Favorite color'
));

Result:

key           | value
-------------- ------
Id              5
Name            John
Favorite color  Orange

Upvotes: 0

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

You can unpivot the columns to get this result as follows:

select fld, val
from (
    select to_char(id) as "Id", -- convert all columns to same type 
            name as "Name",
            some_value as "Favorite color"
      from your_table
     where id = 5
    ) unpivot(val for fld in("Id", "Name", "Favorite color"));

Upvotes: 6

krokodilko
krokodilko

Reputation: 36107

Use simple UNION ALL clause

SELECT 'Id' As field_name, cast( id as varchar2(100)) as Value FROM "TABLE" where id = 5
UNION ALL
SELECT 'Name' , name FROM "TABLE" where id = 5
UNION ALL
SELECT 'Favorite color' , some_value FROM "TABLE" where id = 5;

Upvotes: 0

Related Questions