I have Vehicle Path table in following order

I want result like below format.

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

CodePudding user response：

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;

CodePudding user response：

Another way, assuming SQL Server 2017 or better (and that you don't care about 5th and later rows for any given Vehicle_ID):

;WITH cte AS 
(
  SELECT Vehicle_ID, 
    y = '["'   STRING_AGG(Vehicle_Path, '","')   '"]'
  FROM dbo.VehicleInfo
  GROUP BY Vehicle_ID
)
SELECT Vehicle_ID, 
  Path1 = JSON_VALUE(cte.y, '$[0]'),
  Path2 = JSON_VALUE(cte.y, '$[1]'),
  Path3 = JSON_VALUE(cte.y, '$[2]'),
  Path4 = JSON_VALUE(cte.y, '$[3]')
FROM cte;

• Example db<>fiddle