sql - 将行旋转为 14 列作为 7 个元组

Question

我有一张像这样的 table

assignmentID    personID    projectCode projectCodePercent
-------------- ------------ ----------- ----------- 
642             13527       511         75.00
642             13527       621         25.00
650             12000       555         50.00
650             12000       520         25.00
650             12000       621         25.00
240             56000       721         100.00

我想知道(porjectCode，projectCodePercent)作为每个作业的元组的分布。一个作业最多可以有 7 个元组。如果他们没有 7，那么这些字段可以为空。因此所需的输出应如下所示:

assignmentID    projectCode1  projectCodePercent1   projectCode2   projectCodePercent2   projectCode3   projectCodePercent3   projectCode4   projectCodePercent4   projectCode5   projectCodePercent5   projectCode6   projectCodePercent6   projectCode7   projectCodePercent7
------------    ------------  -------------------   ------------   -------------------   -------------   ------------------- -------------   -------------------   -------------  -------------------   -------------   -------------------  ------------   ----------------
 642            511           75.00                 621            25.00
 650            555           50.00                 520            25.00                 621             25.00
 240            721           100.00

编辑:记录顺序并不重要。 IE。只要正确的projectCode 与正确的projectCodePercent 匹配，哪条记录分配给projectCode1 或projectCode2...等等并不重要

Answer 1

演示:http://rextester.com/IYDJ29385

1. CTE 为我提供了示例数据供我使用
2. CTE2 只是为每个分配 ID 和 PersonId 分配一个行号(您可以只使用内联 View )
3. 然后，我们使用 case 语句根据生成的行号对数据进行透视。这种方法的缺点是总是返回所有 14 列。使用动态 SQL，您只能在需要时显示这些列。

这确实假设分配 ID 和人员 ID 以及项目代码是唯一的。如果同一任务和人员可能存在多个项目代码，那么我们需要做一些不同的事情，而不是最多。

WITH CTE (assignmentID,    personID,    projectCode, projectCodePercent) as  (
SELECT 642,             13527,       511,         75.00 UNION ALL
SELECT 642,             13527,       621,         25.00 UNION ALL
SELECT 650,             12000,       555,         50.00 UNION ALL
SELECT 650,             12000,       520,         25.00 UNION ALL
SELECT 650,             12000,       621,         25.00 UNION ALL
SELECT 240,             56000,       721,         100.00),
cte2 as (SELECT A.*, row_number() over (partition by AssignmentID, PersonID order by projectCode) RN 
      FROM cte A)
SELECT AssignmentID
     , PersonID
     , max(CASE WHEN RN = 1 then projectCode end) as projectCode1
     , max(CASE WHEN RN = 1 then ProjectcodePercent end) as ProjectcodePercent1
     , max(CASE WHEN RN = 2 then projectCode end) as projectCode2
     , max(CASE WHEN RN = 2 then ProjectcodePercent end) as ProjectcodePercent2
     , max(CASE WHEN RN = 3 then projectCode end) as projectCode3
     , max(CASE WHEN RN = 3 then ProjectcodePercent end) as ProjectcodePercent3
     , max(CASE WHEN RN = 4 then projectCode end) as projectCode4
     , max(CASE WHEN RN = 4 then ProjectcodePercent end) as ProjectcodePercent4
     , max(CASE WHEN RN = 5 then projectCode end) as projectCode5
     , max(CASE WHEN RN = 5 then ProjectcodePercent end) as ProjectcodePercent5
     , max(CASE WHEN RN = 6 then projectCode end) as projectCode6
     , max(CASE WHEN RN = 6 then ProjectcodePercent end) as ProjectcodePercent6
     , max(CASE WHEN RN = 7 then projectCode end) as projectCode7
     , max(CASE WHEN RN = 7 then ProjectcodePercent end) as ProjectcodePercent7
FROM CTE2
Group by AssignmentID, personId

给我们:

+----+--------------+----------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+
|    | AssignmentID | PersonID | projectCode1 | ProjectcodePercent1 | projectCode2 | ProjectcodePercent2 | projectCode3 | ProjectcodePercent3 | projectCode4 | ProjectcodePercent4 | projectCode5 | ProjectcodePercent5 | projectCode6 | ProjectcodePercent6 | projectCode7 | ProjectcodePercent7 |
+----+--------------+----------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+
|  1 |          650 |    12000 |          520 |               25,00 | 555          | 50,00               | 621          | 25,00               | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                |
|  2 |          642 |    13527 |          511 |               75,00 | 621          | 25,00               | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                |
|  3 |          240 |    56000 |          721 |              100,00 | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                |
+----+--------------+----------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+