如何运用SQL [英] How to pivot in SQL
本文介绍了如何运用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屋!
查看全文