Search code examples
sqlsql-servert-sqlview

Transpose specific column into single row

I have Vehicle Path table in following order

Vehicle_ID Vehicle Path
1 101
1 55
1 136
2 50
2 65
2 75
3 101
3 105
3 110
3 125

I want result like below format.

Vehicle_ID Path1 Path2 Path3 Path4
1 101 55 136
2 50 65 75
3 101 105 110 125

I tried pivot method but I couldn't get result like above.

Solution

  • Note that there is no third column which maintains the actual relative ordering of the path values. Assuming this order doesn't matter, we can pivot with the help of ROW_NUMBER:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Vehicle_ID
                                 ORDER BY Vehicle_ID) rn
    FROM yourTable
)

SELECT
    Vehicle_ID,
    MAX(CASE WHEN rn = 1 THEN [Vehicle Path] END) AS Path1,
    MAX(CASE WHEN rn = 2 THEN [Vehicle Path] END) AS Path2,
    MAX(CASE WHEN rn = 3 THEN [Vehicle Path] END) AS Path3,
    MAX(CASE WHEN rn = 4 THEN [Vehicle Path] END) AS Path4
FROM cte
GROUP BY
    Vehicle_ID
ORDER BY
    Vehicle_ID;