nth Occurence Join

Amery, Hisham 81 Reputation points
2021-03-19T18:35:20.417+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,274 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.4K Reputation points
    2021-03-19T18:50:21.257+00:00

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

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Amery, Hisham 81 Reputation points
    2021-03-19T19:12:41.413+00:00

    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!

    0 comments No comments