根据SQL Server表中存在的行编号，行分为列

Question

我在SQL Server表中的一组车辆故障零件如下：

车辆_ID	失败零件	ROWNUMBER
1	0001	1
1	0002	1
1	0003 1 1 0001	2
1	0001	2
1 0001 1 2	1 0006	2
1	0007	2
1	0001	3
1	0009	0002 1 2 0002 1 2 0002 1 2 0002 1
3 2	0001	2 0002 1 2 0002 1
2	0002	1 1 2 0002 1
2	0003	2
2	0007	2
3	0006	1
3	0007	1
4	0011	1
4	0015	1
4	0016	1

ROWNUMBER是我们必须定义行的列。结果表如下。 Maximum number of the failed part column count is 5.

Vehicle_ID	Failed Part1	Failed Part2	Failed Part3	Failed Part4	Failed Part5
1	0001	0002	0003
1	0001	0006	0007
1	0001	0009
2	0001	0002
2	0003	0007
3	0006	0007
4	0011	0015	0016

我尝试了以下语句，但无法确切的结果。

WITH cte AS 
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (PARTITION BY Vehicle_ID,RowNumber)
                           ORDER BY [Failed Part]) rn
    FROM 
        VehicleTable
)
SELECT
    Vehicle_ID,
    MAX(CASE WHEN rn = 1 THEN [Failed Part] END) AS FailedPart1,
    MAX(CASE WHEN rn = 2 THEN [Failed Part] END) AS FailedPart2,
    MAX(CASE WHEN rn = 3 THEN [Failed Part] END) AS FailedPart3,
    MAX(CASE WHEN rn = 4 THEN [Failed Part] END) AS FailedPart4,
    MAX(CASE WHEN rn = 5 THEN [Failed Part] END) AS FailedPart5
FROM 
    cte 
GROUP BY
    Vehicle_ID

Answer 1

您的代码中的问题真的很微妙。您正在做的是在ware_id上分组，问题在于，输出表中的每个perhate_id都有多个行。相反，您应该做的是通过在CTE中计算的“ Code> rn ”上分组：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Vehicle_ID, RowNumber
                                 ORDER BY [Failed Part]) rn
    FROM VehicleTable
)
SELECT
    Vehicle_ID,
    MAX(CASE WHEN rn = 1 THEN [Failed Part] END) AS FailedPart1,
    MAX(CASE WHEN rn = 2 THEN [Failed Part] END) AS FailedPart2,
    MAX(CASE WHEN rn = 3 THEN [Failed Part] END) AS FailedPart3,
    MAX(CASE WHEN rn = 4 THEN [Failed Part] END) AS FailedPart4,
    MAX(CASE WHEN rn = 5 THEN [Failed Part] END) AS FailedPart5
FROM cte 
GROUP BY
    Vehicle_ID, rn

如果您希望行排序如输出中，则只需要添加order通过ware_id，rn最后。

尝试