-- sample data

;WITH

MySample (ID, notes, account1, account2, account3, account4, account5) AS (

SELECT 1, 'notes', 1, 0, 1, 0, 1 UNION ALL

SELECT 2, 'notes', 0, 1, 0, 1, 0 UNION ALL

SELECT 3, 'notes', 1, 0, 1, 0, 0),

UserTable (, lastaccounttype) AS (SELECT 'ericb1', 'account1' UNION ALL SELECT 'Yotam Ottolenghi','account5')

-- solution

SELECT s.ID, s.notes, x.*, u.

FROM MySample s

CROSS APPLY (

VALUES

(s.account1,'Account1'),

(s.account2,'Account2'),

(s.account3,'Account3'),

(s.account4,'Account4'),

(s.account5,'Account5')

) x (Flag, [Account])

INNER JOIN UserTable u

ON u.lastaccounttype = x.Account

AND x.Flag = 1

ORDER BY u.