Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


create table t1 (city, code, qty) as
    select trim (c.column_value), trim (s.column_value), rownum*10  
    from table (sys.odciVarchar2List ('101','102','103')) s, 
    table (sys.odciVarchar2List ('aaa','bbb')) c
6 rows affected

select * from t1


CITY
CODE
QTY


aaa
101
10

bbb
101
20

aaa
102
30

bbb
102
40

aaa
103
50

bbb
103
60


select * from (
    select city, code, qty
    from t1)
pivot (sum (qty) for code in ('101', '102', '103'))


CITY
'101'
'102'
'103'


aaa
10
30
50

bbb
20
40
60


insert into t1 values ('aaa', '104', 40)
1 rows affected

select * from (
    select city, code, qty
    from t1)
pivot (sum (qty) for code in (select distinct code from t1))
ORA-00936: missing expression

declare
    cols varchar2 (128);
    stmt varchar2 (32767);
begin
    select listagg (''''||code||''' "'||code||'"', ',') within group (order by code) cols 
    into cols
    from (select distinct code from t1);

    stmt := '
        select * from (
            select city, code, qty
            from t1)
        pivot (sum (qty) for code in ('||cols||'))';
    dbms_output.put_line ('stmt='||stmt);
end;
/
1 rows affected

dbms_output:
stmt=
        select * from (
            select city, code, qty
            from t1)
        pivot (sum (qty) for code in ('101' "101",'102' "102",'103' "103",'104' "104"))

        select * from (
            select city, code, qty
            from t1)
        pivot (sum (qty) for code in ('101' "101",'102' "102",'103' "103",'104' "104"))


CITY
101
102
103
104


aaa
10
30
50
40

bbb
20
40
60
null