Transform Rows into Columns Oracle SQL [duplicate]

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-28 09:42:26Z

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, 2019 at 9:42

answered Oct 28, 2019 at 9:02

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

2 Comments

MT0 Over a year ago

You don't need the nested sub-query. You can just do SELECT * FROM tab PIVOT ...

Barbaros Özhan Over a year ago

you'rewelcome@DropMania

GMB · Accepted Answer · 2019-10-28 08:53:14Z

1

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.

answered Oct 28, 2019 at 8:53

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

Transform Rows into Columns Oracle SQL [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related