MODEL 01A

NameID  Name     EMail
1           aa        john@test.com 
2           bb        rick@test.com
3           cc        sally@test.com  
4           dd        aha@test.com

NameID  Mode
1          C
1          D
2          B
2          D
3          B
3          C
4          A
4          B

MODEL 01A

NameID  Name     EMail
1           aa        john@test.com 
2           bb        rick@test.com
3           cc        sally@test.com  
4           dd        aha@test.com

NameID  Mode
1          C
1          D
2          B
2          D
3          B
3          C
4          A
4          B

DECLARE @t TABLE 
    (NameID INT IDENTITY, name VARCHAR(20), email VARCHAR(20), A INT, B INT, C INT, D INT)

INSERT INTO @t
SELECT 'aa','john@test.com',0, 0, 1, 1
UNION ALL SELECT 'bb','rick@test.com',0, 1, 0, 1
UNION ALL SELECT 'cc','sally@test.com',0, 1, 1, 0
UNION ALL SELECT 'dd','aha@test.com',1, 1, 0, 0

-- MODEL 01:
SELECT * FROM @t

-- MODEL 01A (table 2):
SELECT NameID, Mode
FROM @t 
CROSS APPLY (
    VALUES (CASE A WHEN 1 THEN 'A' ELSE NULL END)
    ,(CASE B WHEN 1 THEN 'B' ELSE NULL END)
    ,(CASE C WHEN 1 THEN 'C' ELSE NULL END)
    ,(CASE D WHEN 1 THEN 'D' ELSE NULL END)) x(Mode)
WHERE Mode IS NOT NULL