sql-server-2005 - "Pivoting"SQL Server 中的非聚合数据

Question

这将是我在这里发布的第一个问题，所以请原谅董事会礼仪中的任何意外失误。

在我当前的项目中，我采用了一个大型的非规范化表并将其分解为四个独立的规范化表。我接触这个委员会的最终目标是创建一个 View ，该 View 模仿非规范化表以实现向后兼容性。

为了提供我的场景的简化快照，我尝试做的关键在于两个表:

ASSOC_ROLE          ASSOCIATE
----------          ----------
assoc_id (fk)       assoc_id (pk)
role_id  (fk)       last_name
org_nbr  (fk)

因此，如果我发出以下查询...

SELECT Assoc_Role.org_nbr, Assoc_Role.assoc_id, Associate.last_name, Assoc_Role.role_id
FROM Assoc_Role INNER JOIN
     Associate ON Assoc_Role.assoc_id = Associate.assoc_id
WHERE Assoc_Role.org_nbr = '1AA'

...我得到以下结果集

org_nbr     assoc_id     last_name     role_id
-------     --------     ---------     -------
1AA         1447         Cooper        1
1AA         1448         Collins       3
1AA         1448         Collins       4
1AA         1448         Collins       5
1AA         1449         Lynch         6

最终，我想构建的 View 看起来像这样:

org_nbr   role1_ID   role1_name   role2_ID   role2_name   role3_ID   role3_name   role4_ID   role4_name   role5_ID   role5_name   role6_ID   role6_name
-------   --------   ----------   --------   ----------   --------   ----------   --------   ----------   --------   ----------   --------   ----------
1AA       1447       Cooper       NULL       NULL         1448       Collins      1448       Collins      1448       Collins      1449       Lynch

我最初的想法是尝试使用 PIVOT 命令，但我的理解是 PIVOT 需要某种聚合，这不适合我的场景。我还在 SELECT 子句中使用了 CASE 命令，但它并没有将我的结果集压缩为一个记录。

希望有人可以阐明我如何实现这一目标。如果有人需要更多信息，请告诉我。谢谢!

苏格兰人

Answer 1

为了获得基本的编号角色数据，我们可以从

SELECT
    org_nbr
    , r1.assoc_id   role1_ID
    , r1.last_name  role1_name
    , r2.assoc_id   role2_ID
    , r2.last_name  role2_name
    , r3.assoc_id   role3_ID
    , r3.last_name  role3_name
    , r4.assoc_id   role4_ID
    , r4.last_name  role4_name
    , r5.assoc_id   role5_ID
    , r5.last_name  role5_name
    , r6.assoc_id   role6_ID
    , r6.last_name  role6_name
FROM
    ASSOC_ROLE ar
    LEFT JOIN ASSOCIATE r1 ON ar.role_id = 1 AND ar.assoc_id = r1.assoc_id
    LEFT JOIN ASSOCIATE r2 ON ar.role_id = 2 AND ar.assoc_id = r2.assoc_id
    LEFT JOIN ASSOCIATE r3 ON ar.role_id = 3 AND ar.assoc_id = r3.assoc_id
    LEFT JOIN ASSOCIATE r4 ON ar.role_id = 4 AND ar.assoc_id = r4.assoc_id
    LEFT JOIN ASSOCIATE r5 ON ar.role_id = 5 AND ar.assoc_id = r5.assoc_id
    LEFT JOIN ASSOCIATE r6 ON ar.role_id = 6 AND ar.assoc_id = r6.assoc_id

但是 这将为我们提供每个 org_nbr , 每个 role_id 单独一行有数据!这不是我们想要的 - 所以我们需要 GROUP BY org_nbr .但是我们需要 GROUP BY或聚合 SELECT 中的每一列列表!诀窍是想出一个聚合函数来安抚 SQL Server 并为我们提供我们想要的结果。在这种情况下， MIN将完成这项工作:

SELECT
    org_nbr
    , MIN(r1.assoc_id)   role1_ID
    , MIN(r1.last_name)  role1_name
    , MIN(r2.assoc_id)   role2_ID
    , MIN(r2.last_name)  role2_name
    , MIN(r3.assoc_id)   role3_ID
    , MIN(r3.last_name)  role3_name
    , MIN(r4.assoc_id)   role4_ID
    , MIN(r4.last_name)  role4_name
    , MIN(r5.assoc_id)   role5_ID
    , MIN(r5.last_name)  role5_name
    , MIN(r6.assoc_id)   role6_ID
    , MIN(r6.last_name)  role6_name
FROM
    ASSOC_ROLE ar
    LEFT JOIN ASSOCIATE r1 ON ar.role_id = 1 AND ar.assoc_id = r1.assoc_id
    LEFT JOIN ASSOCIATE r2 ON ar.role_id = 2 AND ar.assoc_id = r2.assoc_id
    LEFT JOIN ASSOCIATE r3 ON ar.role_id = 3 AND ar.assoc_id = r3.assoc_id
    LEFT JOIN ASSOCIATE r4 ON ar.role_id = 4 AND ar.assoc_id = r4.assoc_id
    LEFT JOIN ASSOCIATE r5 ON ar.role_id = 5 AND ar.assoc_id = r5.assoc_id
    LEFT JOIN ASSOCIATE r6 ON ar.role_id = 6 AND ar.assoc_id = r6.assoc_id
GROUP BY
    org_nbr

输出:

org_nbr    role1_ID    role1_name role2_ID    role2_name role3_ID    role3_name role4_ID    role4_name role5_ID    role5_name role6_ID    role6_name
---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ----------
1AA        1447        Cooper     NULL        NULL       1448        Collins    1448        Collins    1448        Collins    1449        Lynch
Warning: Null value is eliminated by an aggregate or other SET operation.

当然，如果最大值 role_id，这将达不到要求。增加...