如何在SQL转动(How to pivot in SQL)

我不知道这是否会被称为旋转。

在我的SQL 2005表[CustromerRoles]数据是这样：

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

[角色]表：

RoleId  Role
1        Admin
2        Manager
3        Support
4        Assistant

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

SELECT * FROM [MyView的]会给我下面的数据：

在1分0的将是位的，所以我可以显示我的UI显示上的复选框网格。

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

到目前为止，我不知道如何去这样做。

Answer 1:

试试这个：

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

测试。 它给你想要的结果相同。

Answer 2:

你读了有关文件PIVOT在Microsoft SQL Server 2005？

SELECT CustId, 
  [1] AS Admin,
  [2] AS Manager,
  [3] AS Support,
  [4] AS Assistant
FROM (SELECT c.CustId, r.RoleId
FROM CustomerRoles c JOIN Roles r USING (RoleId)) AS s
PIVOT (
 COUNT(CustId)
 FOR RoleId IN ([1], [2], [3], [4])
) AS pvt
ORDER BY CustId;

我没有测试上面，而只是基于它的文档。 这可能让你开始。

似乎没有要动态生成列的方式。 你必须硬编码它们。

Answer 3:

PIVOT有列必须知道的缺点，因为你必须提供查询的ID。 您可以通过使用动态SQL，即动态生成基于角色表中单独的查询结果的PIVOT查询，你的情况，然后执行结果解决这个问题。 这可以很容易地在一个存储过程来完成。

例：

CREATE TABLE #CustomerRole ([CustId] int, [RoleId] int);
INSERT INTO #CustomerRole values (2, 4);
INSERT INTO #CustomerRole values (2, 3);
INSERT INTO #CustomerRole values (3, 4);
INSERT INTO #CustomerRole values (4, 1);
INSERT INTO #CustomerRole values (4, 2);

CREATE TABLE #Role ([Id] int, [Role] varchar(20));
INSERT INTO #Role values (1, 'Admin');
INSERT INTO #Role values (2, 'Manager');
INSERT INTO #Role values (3, 'Support');
INSERT INTO #Role values (4, 'Assistant');

DECLARE @RoleList nvarchar(MAX)
SELECT @RoleList = COALESCE(@RoleList + ',[' + [Role] + ']',
 '[' + [Role] + ']') 
    FROM #Role;

DECLARE @SQL Nvarchar(max);
SET @SQL = 'SELECT 
                [CustId] '  + 
                ISNULL(', ' + @RoleList , '') + ' 
                FROM #CustomerRole custrole
                inner join #Role as r
                on r.[Id] = custrole.[RoleId]
            PIVOT (count([Id]) for [Role] IN 
                (' + ISNULL(@RoleList, '[No role]') +
                 ')) as pvt;' 

EXEC sp_executesql @SQL;

drop table #Role;
drop table #CustomerRole;

文章来源: How to pivot in SQL