CODEWITHSUNDEEP
sqlwindow-functionshanafull-outer-join
kk luo
kk luo

Reputation: 599

how to write a SQL to display distinct values of two columns in one row

I have tables a,b. Table a

ID     In
----------------
1      Mat1
1      Mat2
2      Mat1
3      Mat3

Table b

ID      Out
--------------
 1      Mat4
 2      Mat4
 2      Mat5
 3      Mat6

I want a result like below.

ID     In    Out
------------------
 1    Mat1   Mat4
 1    Mat1
 2    Mat1   Mat4
 2           Mat5
 3    Mat3   Mat6

I think full join can't make empty field in some row.Maybe I need to use Rownum for this? Any help? Thx.

Upvotes: 0

Views: 34

Answers (1)

GMB
GMB

Reputation: 222482

One option is to use row_number() to enumerate the rows, and then full join the results. For this to really make sense, you would need a column in each table to order the records. I assumed ordering_id:

select id, a.in, b.out
from (
    select a.*, row_number() over(partition by id order by ordering_id) rn 
    from tablea a
) a
full join (
    select b.*, row_number() over(partition by id order by ordering_id) rn 
    from tableb b
) b using(id, rn)

Not all database support full joins (and not all of them support using() for join conditions).

A more portable approach is to use union all:

select id, max(in) in, max(out) out
from (
    select id, in, null out, row_number() over(partition by id order by ordering_id) rn 
    from tablea
    union all
    select id, null, out, row_number() over(partition by id order by ordering_id) rn 
    from tableb b
) x
group by id, rn

Upvotes: 1

Related Questions