如何在 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屋！