and this sort of model is one that I a huge anti fan of. (hate it when we give profession sounding acronyms to things like they are "a good thing (tm)"

funny to be an anti-fan of analytics when they prove to be the coolest thing since the keyword SELECT in SQL -- for answering the unanswerable, for answering it fast, for doing things in SQL that required programming. oh well.

you need a very simple pivot.

I really assume your second set of inserts was really to be with the number 2 instead of one...


select deptno, sum(sal)
from (
select max(decode( col_name, 'DEPTNO', value )) deptno,
max(decode( col_name, 'SAL', value )) sal
from t
group by row_id
)
group by deptno;


this is exactly the model I spent a chapter on in my last book making fun of it. Showing how "easy and flexible" such a model truly is, how well it performs (none of that is true, it is neither easy, nor flexible, nor performant -- the easiest questions in the world become the hardest things to answer)

could we do this with analytics? sure, scalar subqueries even. here are three implementations:

select deptno, sum(sal)
from (
select max(decode( col_name, 'DEPTNO', value )) deptno,
max(decode( col_name, 'SAL', value )) sal
from t
where col_name in ('DEPTNO','SAL')
group by row_id
)
group by deptno

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.31 0.34 0 231 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.31 0.34 0 231 0 10

Rows Row Source Operation
------- ---------------------------------------------------
10 SORT GROUP BY (cr=231 r=0 w=0 time=346835 us)
27817 VIEW (cr=231 r=0 w=0 time=305958 us)
27817 SORT GROUP BY (cr=231 r=0 w=0 time=265262 us)
55634 TABLE ACCESS FULL T (cr=231 r=0 w=0 time=60161 us)
********************************************************************************
select deptno, sum(sal)
from (
select max(decode(col_name,'DEPTNO',value)) over (partition by row_id) deptno,
max(decode(col_name,'SAL' ,value)) over (partition by row_id) sal,
row_number() over (partition by row_id order by col_name) rn
from t
where col_name in ('DEPTNO','SAL')
)
where rn = 1
group by deptno

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.43 0.46 0 231 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.43 0.46 0 231 0 10

Rows Row Source Operation
------- ---------------------------------------------------
10 SORT GROUP BY (cr=231 r=0 w=0 time=464361 us)
27817 VIEW (cr=231 r=0 w=0 time=417787 us)
55634 WINDOW SORT (cr=231 r=0 w=0 time=350280 us)
55634 TABLE ACCESS FULL T (cr=231 r=0 w=0 time=118476 us)
********************************************************************************
select deptno, sum(sal)
from (
select value sal,
(select value from t t2 where row_id = t.row_id and col_name = 'DEPTNO') deptno
from t
where col_name = 'SAL'
)
group by deptno

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.48 0.51 0 55867 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.48 0.51 0 55867 0 10

Rows Row Source Operation
------- ---------------------------------------------------
27817 TABLE ACCESS BY INDEX ROWID OBJ#(32233) (cr=55636 r=0 w=0 time=297771 us)
27817 INDEX UNIQUE SCAN OBJ#(32234) (cr=27819 r=0 w=0 time=163237 us)(object id 32234)
10 SORT GROUP BY (cr=55867 r=0 w=0 time=516822 us)
27817 TABLE ACCESS FULL OBJ#(32233) (cr=231 r=0 w=0 time=45530 us)