I have a table:
create table mytab (ROLE_NAME VARCHAR2(30), MENU_TAB VARCHAR2(20), colA VARCHAR2(1), colB VARCHAR2(1), colC VARCHAR2(1));
I populate:
INSERT INTO MYTAB VALUES('TECH','MAIN','A','B', NULL);
INSERT INTO MYTAB VALUES('TECH','SUB','B','C', NULL);
INSERT INTO MYTAB VALUES('TEMP','MAIN','B','A','C');
INSERT INTO MYTAB VALUES('OPER','MAIN',NULL, 'C','B');
SQL> select * from mytab;
ROLE_NAME MENU_TAB colA colB colC
------------------------------ -------------------- ---- ---- ----
TECH MAIN A B NULL
TECH SUB B C NULL
TEMP MAIN B A C
OPER MAIN NULL C B
In my data, ROLE_NAME||MENU_TAB will always be unique.
I'm looking for a way to aggregate the results of the following query into a single row.
SQL> select colA, colB, colC
from mytab
where menu_tab = 'MAIN' and role_name in ('TECH','OPER');
The query returns 2 rows (of course) - With the data I'm working with, it will always return 1 or 2 rows.
colA colB colC
---- ---- ----
A B NULL (<i> From the <TECH> record</i>)
NULL C B (<i> From the <OPER> record</i>)
I want to return a single
aggregated row of all the data found in all records with
priority given to a chosen ROLE_NAME in cases where there is a value in the same column for two rows.
I'm trying to get this:
colA colB colC
---- ---- ----
A C B
In this case, colA and colC are from the 'TECH' record, colB is from the 'OPER' record because I want to designate the 'OPER' data as "priority" in cases where multiple rows have a value in the same column.
Is this possible in a single SQL query? (It's for an Oracle Forms project & needs to become a WHERE clause for a block...) Initially I thought this
should be simple but I'm beginning to believe that it must be
me that's "simple". Any assistance gratefully accepted.
It's possible in one SQL statement. I wouldn't say it's obvious though, so don't be too hard on yourself ;)
The first thing you'll want to do is assign rows a number according to their priority. For example:
row_number() over (order by role_name)
Adjust the order by clause as needed. You can then find the values from the lowest (top) priority row with first_value using the ignore nulls clause
with rws as (
select colA, colB, colC, row_number() over (order by role_name) rn
from mytab
where menu_tab = 'MAIN' and role_name in ('TECH','OPER')
), vals as (
select first_value(colA) ignore nulls over (order by rn) cola,
first_value(colB) ignore nulls over (order by rn) colb,
first_value(colC) ignore nulls over (order by rn) colc
from rws
)
select * from vals;
COLA COLB COLC
C B
A C B
At this point you still have two rows and some nulls. Here are two methods you can use to squash them out and get one row:
Group By & MinMin (or max) ignore the nulls. So you can group by any other columns you need and apply this to the result of the first_value:
with rws as (
select colA, colB, colC, row_number() over (order by role_name) rn
from mytab
where menu_tab = 'MAIN' and role_name in ('TECH','OPER')
), vals as (
select first_value(colA) ignore nulls over (order by rn) cola,
first_value(colB) ignore nulls over (order by rn) colb,
first_value(colC) ignore nulls over (order by rn) colc
from rws
)
select min(cola), min(colb), min(colc) from vals;
MIN(COLA) MIN(COLB) MIN(COLC)
A C B
First_value Windowing ClauseThe default windowing clause stops at the current row. Use "rows between unbounded preceding and unbounded following" to consider all rows in the data set and avoid unwanted nulls:
with rws as (
select colA, colB, colC, row_number() over (order by role_name) rn
from mytab
where menu_tab = 'MAIN' and role_name in ('TECH','OPER')
), vals as (
select first_value(colA) ignore nulls over (order by rn
rows between unbounded preceding and unbounded following) cola,
first_value(colB) ignore nulls over (order by rn
rows between unbounded preceding and unbounded following) colb,
first_value(colC) ignore nulls over (order by rn
rows between unbounded preceding and unbounded following) colc
from rws
)
select * from vals;
COLA COLB COLC
A C B
A C B
You then need to group by/distinct the results so you only get one row.