I think an easy options is to Pivot using the UNPIVOT statement

This will transpose your Account1, Account2 columns into one column called "AccountType", then you can do a join on AccountType using the LastAccountType of your 2nd table.

So for your query it would look something like this:

;WITH cte as -- Used a CTE to generate the sample values in your 1st table

(SELECT *

FROM

(VALUES(1, 'notes', 1, 0, 1, 0, 1),(2, 'notes', 1, 0, 1, 0, 1),(3, 'notes', 1, 0, 1, 0, 1)) as t(id,note,ac1,ac2,ac3,ac4,ac5) --SAMPLE VALUES

)

SELECT id, note, AccountTypeID,IncludeNote

FROM

(SELECT id, note, ac1, ac2, ac3, ac4, ac5

FROM cte) p

UNPIVOT

(IncludeNote FOR AccountTypeID IN

(ac1, ac2, ac3, ac4, ac5))AS unpvt;

Then you can just join on AccountTypeID using the 2nd table, and filter by IncludeNote

Hope this helps!