将每个用户的现有角色显示到列(行到列)

CREATE TABLE #Table1
    ([ID] int, [Role] varchar(8))
;

INSERT INTO #Table1
    ([ID], [Role])
VALUES
    (1, 'Admin'),
    (2, 'Verifier'),
    (3, 'Approver')
;


CREATE TABLE #Table2
    ([UserID] int, [RoleID] int)
;

INSERT INTO #Table2
    ([UserID], [RoleID])
VALUES
    (1, 1),
    (1, 2),
    (2, 3),
    (3, 2),
    (3, 3)

SELECT UserID
    ,max(CASE 
        WHEN ROLE = 'Admin'
            THEN 'YES'
        ELSE 'NO'
        END) Admin
    ,max(CASE 
        WHEN ROLE = 'Verifier'
            THEN 'YES'
        ELSE 'NO'
        END) Verifier
    ,max(CASE 
        WHEN ROLE = 'Approver'
            THEN 'YES'
        ELSE 'NO'
        END) Approver
FROM #Table1 a
JOIN #Table2 B ON a.ID = b.RoleID
GROUP BY UserID

输出

UserID | Admin | Verifier | Approver

1 | Yes | Yes | No

2 | No | No | Yes

3 | No | Yes | Yes