SQL & PL/SQL

This thread started just for fun. To share (and perhaps discuss?) some unusual ways of applying SQL features.

Using **model** instead of **pivot/unpivot**.

with cars(client, make, model, cnt) as
(
    select 1, 'BMW', 'X5', 1 from dual
    union all select 1, 'BMW', 'X7', 5 from dual
    union all select 2, 'BMW', 'X', 10 from dual
    union all select 2, 'BMW', 'Z4', 2 from dual
    union all select 1, 'Audi', 'Q5', 2 from dual
    union all select 1, 'Audi', 'Q8', 1 from dual
    union all select 1, 'Audi', 'TT', 3 from dual
    union all select 2, 'Audi', 'Q8', 4 from dual
)
select *
from (select client, make, cnt from cars)
pivot (sum(cnt) for make in ('BMW' as bmw, 'Audi' as audi));

    CLIENT        BMW       AUDI
---------- ---------- ----------
         1          6          6
         2         12          4

Same can be achieved with

select client, bmw, audi
from cars
model
return updated rows
partition by (client)
dimension by (make, rownum "_")
measures (0 bmw, 0 audi, cnt) 
rules
(
    bmw[0, 0] = sum(cnt)['BMW', any],
    audi[0, 0] = sum(cnt)['Audi', any]    
);

Similarly below

with cars_pvt(client, bmw, audi) as
(
    select 1, 6, 6 from dual
    union all select 2, 12, 4 from dual
)
select *
from cars_pvt
unpivot (cnt for type in (bmw as 'BMW', audi as 'Audi'));

    CLIENT TYPE        CNT
---------- ---- ----------
         1 BMW           6
         1 Audi          6
         2 BMW          12
         2 Audi          4

Can be rewritten to

select client, make, cnt
from cars_pvt
model
return updated rows
partition by (client)
dimension by ('dummy' make)
measures (bmw, audi, 0 cnt)
rules
(
    cnt['BMW'] = bmw['dummy'],
    cnt['Audi'] = audi['dummy']
);

Is there any practical reason to use model instead of pivot/unpivot?

I do not think so.