How to create a pivot tablefor this problem:

This is my original table:

Original table

And I would like it to be as:

CityID | 1 | 2 | 3 | 4 | 5 | 6 | 7
_____________________________________
1024    0800 0900 and so on...

Here is my code, but I get a syntax error near FOR.

select * from
(select SIDURI as CityID, DAY as ArrivalDay, T_FROM as TimeArrival
from RNFIL488)  as timingTable
pivot(
timing.SIDURI as CityID
timingTable.T_FROM as TimeArrival
for timing.DAY as ArrivalDay in (
[1],
[2],
[3],
[4],
[5],
[6],
[7]
)
) as pivot_table 

Answer

Use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CityID ORDER BY ArrivalDay) rn
    FROM RNFIL488
)

SELECT
    CityID,
    MAX(CASE WHEN rn = 1 THEN TimeArrival END) AS [1],
    MAX(CASE WHEN rn = 2 THEN TimeArrival END) AS [2],
    MAX(CASE WHEN rn = 3 THEN TimeArrival END) AS [3],
    MAX(CASE WHEN rn = 4 THEN TimeArrival END) AS [4],
    MAX(CASE WHEN rn = 5 THEN TimeArrival END) AS [5],
    MAX(CASE WHEN rn = 6 THEN TimeArrival END) AS [6],
    MAX(CASE WHEN rn = 7 THEN TimeArrival END) AS [7]
FROM cte
GROUP BY
    CityID;

This assumes that your original source table would always have 7 arrival days per city. If not, then we might have to use a calendar table to bring in the missing data. Also, I am avoiding the PIVOT operator, because often the above approach performs better (and I also find it much easier to read).