我需要从“ userrole”表中提取一些系统用户数据到配置记录中,但是这些用户权限保存在单个列中,并由其他角色标识。
所以我的userrole数据表如下所示,
UserID RoleID Discriminator
int int NVarChar
3483 1 Pathologist
3483 2 Histotech
3483 3 Configuration
3483 4 WebViewer
3484 1 Pathologist
3484 4 WebViewer
3485 1 Pathologist
3485 4 WebViewer
3487 1 Pathologist
3487 2 Histotech
3487 3 Configuration
3487 4 WebViewer
3488 1 Pathologist
3488 2 Histotech
3488 3 Configuration
3488 4 WebViewer
我的目标结果是
3483 Pathologist Histotech Configuration WebViewer
3484 Pathologist WebViewer
3484 Pathologist WebViewer
3487 Pathologist Histotech Configuration WebViewer
等
例如,每次尝试“分组依据”时,仍然会返回多行
select USERID,(select Discriminator where roleid = 1) as Pathologist,(select Discriminator where roleid = 2) as Histologist,(select Discriminator where roleid = 3) as Configuration,(select Discriminator where roleid = 4) as Web
FROM [Workflow].[UserRole]
group by userid,RoleID,discriminator
给予
USERID Pathologist Histologist Configuration Web
3483 Pathologist NULL NULL NULL
3483 NULL Histotech NULL NULL
3483 NULL NULL Configuration NULL
3483 NULL NULL NULL WebViewer
3484 Pathologist NULL NULL NULL
3484 NULL NULL NULL WebViewer
3485 Pathologist NULL NULL NULL
3485 NULL NULL NULL WebViewer
按照SQL Query Multiple Columns Using Distinct on One Column Only中的建议尝试在用户ID上使用DISTINCT或MIN函数(我所知道的情况不太一样)仍然会给我相同的多行结果。
如果对接下来的尝试有什么看法,我已经有点犹豫了,因此,非常感谢收到任何建议。