如何运用SQL [英] How to pivot in SQL

问题描述

我不确定这是否称为透视".

I am not sure if this would be called pivoting.

我的SQL 2005表 [CustromerRoles] 中的数据是这样的:

Data in my SQL 2005 table [CustromerRoles] is as such:

CustId  RoleId
2        4
2        3
3        4
4        1
4        2

[角色] 表:

RoleId  Role
1        Admin
2        Manager
3        Support
4        Assistant

我想创建一个这样的视图:

I want to create a view such that:

SELECT * FROM [MYVIEW]将给我以下数据:

SELECT * FROM [MYVIEW] will give me the data below:

1& 0是位，因此我可以在UI显示屏上显示带有复选框的网格.

The 1 & 0's will be bits so that I can display a grid with checkboxes on my UI display.

CustId  Admin Manager Support Assistant
2         0     0       1        1
3         0     0       0        1
4         1     1       0        0

到目前为止，我还不知道如何去做.

So far I have no idea how to go about doing this.

推荐答案

尝试一下:

SELECT 
    CustId, 
    SUM(ISNULL(Admin,0)) AS Admin, 
    SUM(ISNULL(Manager,0)) AS Manager, 
    SUM(ISNULL(Support,0)) AS Support, 
    SUM(ISNULL(Assistant,0)) AS Assistant
FROM
(
    SELECT cr.CustId, cr.RoleId, Role, 1 AS a
    FROM CustromerRoles cr
    INNER JOIN Roles r ON cr.RoleId = r.RoleId
) up
PIVOT (MAX(a) FOR Role IN (Admin, Manager, Support, Assistant)) AS pvt
GROUP BY CustId

经过测试.它会提供您想要的相同结果.

Tested. It gives the same result you want.

这篇关于如何运用SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！