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

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Philipp.

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

Answered by: Chris Saxon - Last updated: October 23, 2019 - 4:32 pm UTC

Category: SQL - Version: Oracle 12.1.0.2.0 (64 Bit)

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Apex patch for 5.1

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 we 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  

and you rated our response

  (1 rating)

Reviews

Excellent!

October 24, 2019 - 5:52 am UTC

Reviewer: Philipp

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.