Transpose rows into columns using Pivot in Oracle

Question

I have a select query that returns results like below.

I want to update the query to transpose the rows into columns to make it look like the Output table --

I have tried using Pivot but it is not working. Appreciate any help. Thanks

I got the required output. However, if there are multiple values for same ID and fieldname, how to get all of them. Sample i/p and o/p is below.

I/P:

O/P:

Answer 1

With pivot you could do:

select *
from your_table
pivot (
  max(coalesce(text, to_char(num)))
  for (fieldname) in ('Width' as width, 'Height' as height, 'Comments' as comments))

ID   WIDTH HEIGHT COMMENTS
---- ----- ------ ---------
1051 121   2      FP-SK/124
1170 5678  5

I've used max(coalesce(text, to_char(num))) because you have two columns to cram into one, effectively, and you need to_char() because they are different data types. If a row could have a value in both columns then the value you end up with might not be what you want, but then you'd need to define what should happen in that case.

You could also use conditional aggregation, which is what pivot does under the hood anyway; here simplified to not coalesce, on the assumption you won't have both columns populated:

select id,
  max(case when fieldname = 'Width' then text end) as width,
  max(case when fieldname = 'Height' then num end) as height,
  max(case when fieldname = 'Comments' then text end) as comments
from your_table
group by id

ID   WIDTH HEIGHT COMMENTS
---- ----- ------ ---------
1051 121        2 FP-SK/124
1170 5678       5

db<>fiddle

Notice that the height value is now a number; in the pivot version it is - and must be - a string. You can convert the result to a different data type of course.

How do we get multiple values in the output separated by commas

You can change from max() to listagg():

select *
from your_table
pivot (
  listagg(coalesce(text, to_char(num)), ',')
  for (fieldname) in ('Width' as width, 'Height' as height, 'Comments' as comments))

or

select id,
  listagg(case when fieldname = 'Width' then text end, ',') within group (order by text) as width,
  listagg(case when fieldname = 'Height' then num end, ',') within group (order by text) as height,
  listagg(case when fieldname = 'Comments' then text end, ',') within group (order by text) as comments
from your_table
group by id

which both get

  ID WIDTH      HEIGHT     COMMENTS
---- ---------- ---------- ----------
1051 121,95                Sample
1170 5678       2,5

db<>fiddle