nth Occurence Join

Question

I am very stuck with this. I have two tables:

Table 1: Patients and CPT values that all say SURG

PATNO	CPT
58903	SURG
58903	SURG

Table 2: Has same patients but with actual codes I need to match with Table 1.

PATNO	CPT
58903	29882
58903	6447

The issue: Vlookup only gets first lookup value and I need to populate all the occurrences, not just the first. For Patient 58903, I would like my vlookup to populate the first instance with 29882 and the second with 6447 if possible. I was thinking I could do this easier with SQL? Maybe a left join with a condition but I'm not quite sure how. Any help would be very, very appreciated.

Answer 1

Maybe you need a query like this:

;
with Q1 as
(
    select *, row_number() over (partition by PATNO order by ID) n
    from Table1
),
Q2 as
(
    select *, row_number() over (partition by PATNO order by ID) n
    from Table2
)
select isnull(Q1.PATNO, Q2.PATNO) as PATNO, Q1.CPT as CPT1, Q2.CPT as CPT2
from Q1
full outer join Q2 on Q2.PATNO = Q1.PATNO and Q2.n = Q1.n
order by Q1.PATNO, Q2.PATNO, Q1.n, Q2.n

where ID is the primary key of tables. (Use the real column name instead of ID, or give details, or use other columns that allow precise ordering).

Answer 2

Thank you! This is almost working, there are some random ones it doesn't pull but this is definitely the right track. Seriously, I appreciate it!