Hi!
I was thinking a while about the below task, but could not find an easy / elegant solution. GROUP BY is done by colum names. What I'd like to do could be described by GROUP BY using wildcards or regular expression:
Example:
create table tmp (
key1 varchar(9),
key2 varchar(9),
val number
);
insert into tmp values ('a','x',1);
insert into tmp values ('.','x',2);
insert into tmp values ('.','y',3);
insert into tmp values ('b','y',4);
insert into tmp values ('a','y',5);
select key1,key2, sum(val) from tmp group by key1,key2;
of course returns:
| key1 | key2 | sum(val) |
|---|
| 1 | a | x | 1 |
| 2 | . | x | 2 |
| 3 | b | y | 4 |
| 4 | . | y | 3 |
| 5 | a | y | 5 |
However, consider a column value of '.' to be a wildcard, matching any other key in that column. I.e. the pair (.,x) in row 2 should also match row 1 and the pair (.,y) in row 4 should also match row 3 and row 5 when sum(val) is created.So, I'd like to see this result:
| key1 | key2 | sum(val) |
|---|
| 1 | a | x | 3 |
| 2 | a | y | 8 |
| 3 | b | y | 7 |
I could partiall create the the above via...
select
key1, key2, sum(val)
from (
select
'a' as key1,
key2,
val
from
tmp c
where
key1 in ('a','.')
);
But this will return only the values for key1='a'. And this is hard coded in the query.
Any pointer how to do that without hard-coding the columns / column values in the query?
Please note: This is a simplified example. I need a solution for any number of colums and column values...
Thx for your help!
.
.