Hi,
I have 27 different queries, all of which yield 3 rows max. Something like this:
SELECT CUSTOMER_NUMBER FROM customer WHERE name = 'SomeName' order by CUSTOMER_NUMBER fetch first 3 rows only;
and
SELECT CUSTOMER_NUMBER FROM customer WHERE dateOfBirth < TO_DATE('01.01.2000', 'dd.mm.yyyy') order by CUSTOMER_NUMBER fetch first 3 rows only;
Obviously, the where clause can be much more complicated, but all queries return (at most 3) CUSTOMER_NUMBER rows.
How can I display all of the queries _in one table_ like so:
Customer Number 1 Customer Number 2 Customer Number 3
Query 1 a b c
Query 2 d e f
Query 3 g h
...
Is this possible? (I'd be happy with a transposed table, too, if that's any easier).
Note that if I simply use "UNION" or "WITH", I get the three rows from Query 1 _times_ the three rows from Query 2...
Thanks a lot!
Philipp
You can union all the queries together and pivot the results:
with rws as (
(
select distinct 'Q1' q, owner from dba_tables
order by owner
fetch first 3 rows only
) union all (
select distinct 'Q2' q, sequence_owner
from dba_sequences
order by sequence_owner
fetch first 3 rows only
)
), rns as (
select q, owner, row_number () over (
partition by q order by owner
) rn
from rws
)
select * from rns
pivot (
min ( owner ) for rn in (
1, 2, 3
)
);
Q 1 2 3
Q1 APPQOSSYS AUDSYS CHRIS
Q2 CHRIS CO CTXSYS