I have two tables

First Table

id  f1
1   a
1   b   
2   c
3   d
3   e

Second Table

id  f2  
1   k
2   m
2   n
3   p

And I want

id  f1  f2
1   a   k
1   b   
2   c   m
2       n
3   d   p
3   e

As a result is a table with two independent columns (f1 and f2) that shared one reference column (id) and when a column (f1 or f2) has a smaller number of records with the same code then the cells are empty.

CodePudding user response：

In SQL Server, you can use the row_number window function to generate another column that can be used to join the two tables.

I'm not familiar enough with Access to know whether it supports window functions or if there is an access-specific equivalent.

     -- your data tables
with t1 as (select * from (values (1,'a'),(1,'b'),(2,'c'),(3,'d'),(3,'e')) t(id,f1))
    ,t2 as (select * from (values (1,'k'),(2,'m'),(2,'n'),(3,'p')        ) t(id,f2))

     -- your data with sequence counter for each ID
    ,t1Seq as (select *, seq = row_number() over (partition by id order by f1) from t1)
    ,t2Seq as (select *, seq = row_number() over (partition by id order by f2) from t2)

-- generate your desired rowset by using a full join
-- on the id and the generated sequence number within each id value
select id = coalesce(t1Seq.id,t2Seq.id)
      ,t1Seq.f1
      ,t2Seq.f2
from           t1Seq
     full join t2Seq on t2Seq.id = t1Seq.id and t2Seq.seq = t1Seq.seq
;