在SQL中连接两个表并为带有布尔值的varchar创建PIVOT

Question

使用一个用户可以拥有一个或多个角色的SQL Server 2008表

UserDetails :

 username  level       country      role
 =============================================
  john        A          USA      SystemAdmin
  john        B          Brazil   Co-ordinator
  Smith       G          Spain      Doctor
  Anne        D          USA        Nurse
  ....        ...        ....       ....

RoleDetails :

 role            function
 ============================
  SystemAdmin      full time    
  Doctor           part time    
  Co-ordinator     consultant    
  Nurse            On call    
  ....        ...  

我试图创建一个视图，其中的数据看起来像

 username  level       country   SystemAdmin     Co-ordinator    Doctor  Nurse
 =============================================================================
  john        A          USA          1              0             0       0
  john        B          Brazil       0              1             0       0
  Smith       G          Spain        0              0             1       0
  Anne        D          USA          0              0             0       1
  ....        ...        ....       ....             ....          ....   ...

我要做的是连接两个表，并从第二个表的行生成列，其中它们都是在UserDetails.role = RoleDetails.role的基础上连接的。并且大多数列都是数据库中的varchar。我试图动态地从具有布尔值的RoleDetails行生成列。由于RoleDetails表将继续增长，我不能像PIVOT ( MAX(role) FOR role IN (Doctor, Nurse...))那样选择单独的行

不确定这是否可行或如何做到这一点。任何指示将不胜感激。

Answer 1

这是一个非常常见的问题，下面是典型的解决方法。如果需要动态处理角色列表，那么在构建列列表时，可以使用动态SQL和XML特性来完成字符串连接，从而找到许多解决方案。

select
    u.username,
    min(u.level) as level,
    min(u.country) as country,
    min(case when role = 'SystemAdmin'  then 1 else 0 end) as SystemAdmin,
    min(case when role = 'Co-ordinator' then 1 else 0 end) as "Co-ordinator",
    min(case when role = 'Doctor'       then 1 else 0 end) as Doctor,
    min(case when role = 'Nurse'        then 1 else 0 end) as Nurse
from UserDetails u left outer join RoleDetails r
    on r.role = u.role
group by u.username

如果您可以严格限制角色的数量，那么您的应用程序可能能够避免这样的事情。根据您打算如何使用它，使用静态列名可能更可取。

with NumberedRoles as (
    select rolename, row_number() over (order by role) as rn
    from RoleDetails
)
select
    u.username,
    min(u.level) as level,
    min(u.country) as country,
    min(case when r.rn = 1 then 1        else 0 end)    as RoleIsMember01,
    min(case when r.rn = 1 then r."role" else null end) as RoleName01,
    min(case when r.rn = 1 then 1        else 0 end)    as RoleIsMember02,
    min(case when r.rn = 1 then r."role" else null end) as RoleName02,
    min(case when r.rn = 1 then 1        else 0 end)    as RoleIsMember03,
    min(case when r.rn = 1 then r."role" else null end) as RoleName03,
    ...
    min(case when r.rn = 1 then 1        else 0 end)    as RoleIsMember32,
    min(case when r.rn = 1 then r."role" else null end) as RoleName32
from
    UserDetails u inner join
    NumberedRoles r
        on r."role" = u."role"
group by u.username