CODEWITHSUNDEEP
sqlperformanceoracle-database
Khristian Liahut
Khristian Liahut

Reputation: 305

Unifying Queries with different columns in the same record

This is my question:

I have several queries, where each query retrieve me 2 columns.

Example:

1.- Client - Dahi

2.- Client - Patr

3.- Client - Orts

. . . .

45.-Client - Tyre

Always retrieve the client with an different column.

The result for the query if I make a union is like this: (Of course, Many rows with he same client but differents values of column)

Client    Dahi    Patr    Orts  .........   Tyre
  1         x
  1                         x
  2                 x                         
  3         x
  3                 x           
  3                                           x

I would like to unify the query and retrieve like this (One column per client, unifying the different column in one record):

Client    Dahi    Patr    Orts  .........   Tyre
  1         x               x
  2                 x
  3         x       x                         x

Some ideas?

Upvotes: 0

Views: 486

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You want to do a full outer join. Here is an example for four queries:

select coalesce(q1.client, q2.client, q3.client, q4.client) as client,
       q1.Dahi, q2.Patr, q3.Orts, q4.Tyre
from q1 full outer join
     q2
     on q2.client = q1.client full outer join
     q3
     on q3.client = coalesce(q1.client, q2.client) full outer join
     q4
     on q4.client = coalesce(q1.client, q2.client, q3.client);

One problem with full outer join is that the client column could be NULL for a previous table/subquery. That is why the coalesce() is needed for the join condition.

This query can be simplified a bit if you have a list of client . . . or if you calculate one for all the subqueries. Here is an example:

select coalesce(q1.client, q2.client, q3.client, q4.client) as client,
       q1.Dahi, q2.Patr, q3.Orts, q4.Tyre
from (select client from q1 union
      select client from q2 union 
      select client from q3 union
      select client from q4
     ) driver left outer join
     q1
     on q1.client = driver.client left outer join
     q2
     on q2.client = driver.client left outer join
     q3
     on q3.client = driver.client left outer join
     q4
     on q4.client = driver.client;

The subquery gets a list of all clients (even better if this is in a separate table). The subsequent left outer joins bring in one table at a time.

Upvotes: 1

Related Questions