Search code examples
oraclepivotoracle-sqldeveloper

Transpose rows into columns using Pivot in Oracle

I have a select query that returns results like below.

enter image description here

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

enter image description here

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:

enter image description here

O/P:

enter image description here

Solution

  • 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