Transform Rows into Columns Oracle SQL

Question

Is there a quick way to transfom a Table like this:

FIELD   |  VALUE
+-------+----------+
Address |  here
Name    |  Rainer
Tel     |  01234567
Other   |  idk

Into this:

Address | Name     | Tel     | Other
+-------+----------+---------+----------+
here    |  Rainer  | 01234567| idk

Barbaros Özhan · Accepted Answer · 2019-10-28T09:42:26.790

2

You can also use pivot method :

select * 
  from tab
 pivot(
       max(value) for field in ( 'Address' as "Address", 
                                 'Name'    as "Name", 
                                 'Tel'     as "Tel", 
                                 'Other'   as "Other" ) )

Demo

edited Oct 28 '19 at 09:42

answered Oct 28 '19 at 09:02

Barbaros Özhan

47,993
9
26
51

You don't need the nested sub-query. You can just do `SELECT * FROM tab PIVOT ...` – MT0 Oct 28 '19 at 09:30
yes, thank you @MT0 . – Barbaros Özhan Oct 28 '19 at 09:43
you'rewelcome@DropMania – Barbaros Özhan Oct 28 '19 at 10:43

score 1 · Answer 2 · answered Oct 28 '19 at 08:53

Use conditional aggregation:

select
    max(case when field = 'Address' then value end) Address,
    max(case when field = 'Name' then value end) Name
    max(case when field = 'Tel' then value end) Tel
    max(case when field = 'Other' then value end) Other
from mytable

In normal situation, you would need a column to group by, like for example a user id, so you can generate several records from the initial content.

Transform Rows into Columns Oracle SQL

2 Answers2