Row items into column based on row number present in SQL Server table-CodePudding

I have set of vehicle failed parts in a SQL Server table as per below:

Vehicle_ID	Failed Part	RowNumber
1	0001	1
1	0002	1
1	0003	1
1	0001	2
1	0006	2
1	0007	2
1	0001	3
1	0009	3
2	0001	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 is the column we have to define the row. The results table like below. Maximum number of the failed part column count is 5.

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

I tried below statement but could not arrive exact results.

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

CodePudding user response：

The issue in your code is really subtle. What you're doing is grouping on Vehicle_ID, problem is that there is more than one row for each Vehicle_ID in the output table. What you should do instead is fixing it by grouping also on the column number rn that you computed in the cte:

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

If you want your rows ordered as in the output, you just need to add ORDER BY Vehicle_ID, rn at the end.

Try it here.