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.
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>
