Oracle SQL Developer: How to Transpose Rows to Columns Using Pivot Function

Oracle SQL Developer: How to transpose rows to columns using PIVOT function

You have a stray semi-colon in your statement, after:

    WHERE partyId = 100; 

Remove that to make it:

SELECT * FROM 
  ( 
    SELECT partyId, contacttext, contacttypecd 
    FROM CONTACT 
    WHERE partyId = 100
  ) 
  PIVOT ( 
    MAX(contacttext) 
  FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email));

   PARTYID PHONE        FAX          MOBILE       EMAIL      
---------- ------------ ------------ ------------ ------------
       100 0354441010   0355551010   0428105789   abc@home.com

It's being seen as multiple statements; the first is incomplete because it's missing a closing parenthesis (so gets ORA-00907), the second starts with that parenthesis and gets the error you reported, and then each subsequent line gets the same error. You only seem to be looking at the last reported error - it's usually much more helpful to start with the first error, clear that, and then move onto the next if it still exists.

Transpose rows into columns using Pivot in Oracle

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

oracle transposing text value rows to columns

I find it much easier just to use conditional aggregation:

select id,
       max(case when type = 'A' then value end) as a,
       max(case when type = 'B' then value end) as b,
       max(case when type = 'C' then value end) as c
from t
group by id;

You can insert the results into a table using create table as. That should work with a pivot query as well.

Rows to columns using PIVOT function (Oracle)

You were nearly done - the only missing point is - you should replace the MAX aggregate function with the LISTAGG function using the full syntax in the PIVOT clause.

Additionaly I adjusted the pivot column names to get nice names (without apostrophes).

See example below:

select * from (
   select ID,
          SUBJECT,GRADE
   from tab
             )
pivot 
(
   LISTAGG(GRADE,',') within group (order by GRADE)
   for SUBJECT in
   ('MTH111' as MTH111,
    'WRI001' as WRI001,
    'PHY104' as PHY104)
)

Result as expected

ID     MTH111     WRI001     PHY104    
------ ---------- ---------- ----------
000442 B,W,W      C-         C,W    

Oracle SQL to convert rows to columns

You can use row_number() and conditional aggregation:

select id,
       max(case when seqnum = 1 then docnum end) as docnum_1,
       max(case when seqnum = 1 then amount end) as amount_1,
       max(case when seqnum = 2 then docnum end) as docnum_2,
       max(case when seqnum = 2 then amount end) as amount_2,
       max(case when seqnum = 3 then docnum end) as docnum_3,
       max(case when seqnum = 3 then amount end) as amount_3
from (select t.*,
             row_number() over (partition by id order by due_date desc) as seqnum
      from t
      where status = 1
     ) t
group by id;

How do I transpose rows into columns

Going off of what Jon Heller said in the comments, you can use a query like the one below to achieve your result.

  SELECT customer_id,
         MAX (CASE WHEN interaction_code IN (113, 152) THEN 1 ELSE 0 END)          AS has_online,
         MAX (CASE WHEN interaction_code IN (101, 102, 107) THEN 1 ELSE 0 END)     AS has_phone
    FROM interactions
GROUP BY customer_id
ORDER BY customer_id;

Transpose rows to columns in Oracle Sql

Something like this?

SQL> select
  2    id,
  3    max(decode(things, 'Food' , descr)) as food,
  4    max(decode(things, 'Cars' , descr)) as cars,
  5    max(decode(things, 'Sport', descr)) as sport
  6  from abc
  7  group by id
  8  order by id;

        ID FOOD       CARS       SPORT
---------- ---------- ---------- ----------
         1 Chicken    BMW        Soccer
         2 Mutton     Ford       Tennis

SQL>

---

As you asked for PL/SQL, a function that returns refcursor might be one option:

SQL> create or replace function f_abc return sys_refcursor is
  2    l_rc sys_refcursor;
  3  begin
  4    open l_rc for
  5      select
  6        id,
  7        max(decode(things, 'Food' , descr)) as food,
  8        max(decode(things, 'Cars' , descr)) as cars,
  9        max(decode(things, 'Sport', descr)) as sport
 10      from abc
 11      group by id
 12      order by id;
 13    return l_rc;
 14  end;
 15  /

Function created.

SQL> select f_abc from dual;

F_ABC
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

        ID FOOD       CARS       SPORT
---------- ---------- ---------- ----------
         1 Chicken    BMW        Soccer
         2 Mutton     Ford       Tennis

SQL>

---

Related Topics