I want the items in rows to be columns.
I have already set up a DBFiddle with the data. Link: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=122a2865fb645e2e226928e28284c590
This is the table I have.
I want the output to be the following:
How to do this in Oracle SQL? The Sequence and the Primary key is making this confusing for me.
Advertisement
Answer
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>