Transpose functionality in Oracle

Question

I need help with SQL query to achieve the below transpose feature, I have a table of entries like,

to

Tried with the Pivotand Decode command but it's giving only one id of a specific type.

Is there any other command i can use to achieve this output. Thanks in advance

Regards, Raghavan

This is a key/value table. They are a nuisance to work with. Aggregation, as shown in Popeye's answer is the typical way to deal with them. But data access is usually rather slow and data is prone to inconsistencies, misspellings, wrong data types, missing attributes. If the data model can be changed, I would consider changing it. — Thorsten Kettner, Jan 15 '21 at 09:09

score 2 · Accepted Answer · answered Jan 15 '21 at 08:52

Simplest method is to use the conditional aggregation as follows:

select property_id,
       max(case when id = 'ADDR1' then text end) as ADDR1,
       max(case when id = 'ADDR2' then text end) as ADDR2,
       max(case when id = 'CITY' then text end) as CITY,
       max(case when id = 'FAX' then text end) as FAX,
       max(case when id = 'LONGNM' then text end) as LONGNM,
       max(case when id = 'NAME' then text end) as NAME
  from your_table
 group by property_id

Transpose functionality in Oracle

1 Answers1