'Row items into column based on row number present in SQL Server table
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 | 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 |
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
