在SQL中使用boolean值连接两个表并为varchar创建PIVOT

Working with SQL Server 2008 tables where A user can have one or multiple roles

使用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    
  ....        ...  

I am trying to create a VIEW where data would look like

我正在尝试创建一个数据看起来像的VIEW

 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
  ....        ...        ....       ....             ....          ....   ...

What I am trying to do is join two tables and generate columns from the rows of the second table where both of them are joined on the basis of UserDetails.role = RoleDetails.role. And most of the columns are varchar in the database. I am trying to generate the Columns from RoleDetails rows with boolean value dynamically. Since RoleDetails table will continue growing, I could not select the individual row like PIVOT ( MAX(role) FOR role IN (Doctor, Nurse...))

我想要做的是连接两个表并从第二个表的行生成列,其中两个表基于UserDetails.role = RoleDetails.role连接。并且大多数列都是数据库中的varchar。我试图动态地使用布尔值从RoleDetails行生成列。由于RoleDetails表将继续增长,我无法选择像PIVOT(MAX(角色)FOR角色IN(Doctor,Nurse ...))这样的单独行

Not sure if this is feasible or how to do it. Any direction would be appreciated.

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

1 个解决方案

#1

This is a very common question and here is the typical way to do this. If you need to handle the list of roles dynamically then you'll find many solutions out there using dynamic SQL along with XML features to accomplish string concatenation when building the column list.

这是一个非常常见的问题,这是执行此操作的典型方法。如果您需要动态处理角色列表,那么您将在构建列列表时使用动态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

Your application may be able to get away with something like this if you can part a hard limit on the number of roles. Depending on how you intend to use this it may be preferable to have static column names anyway.

如果您可以对角色数量进行严格限制,那么您的应用程序可能会逃脱这样的事情。根据您打算如何使用它,最好还是有静态列名。

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