Skip to Main Content
  • Questions
  • Can I get a single table where each column consists of a query (each query returning three rows with ids)?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Philipp.

Asked: October 23, 2019 - 11:48 am UTC

Last updated: October 23, 2019 - 4:32 pm UTC

Version: Oracle 12.1.0.2.0 (64 Bit)

Viewed 1000+ times

You Asked

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

and Chris said...

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  

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent!

Philipp, October 24, 2019 - 5:52 am UTC

Thank you, that was exactly what I needed.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.