question

AmeryHisham-2893 avatar image
0 Votes"
AmeryHisham-2893 asked ·

nth Occurence Join

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.

sql-server-generalsql-server-transact-sql
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered ·

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).

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeryHisham-2893 avatar image
0 Votes"
AmeryHisham-2893 answered ·

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!

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.