columns in SQL Server

I have a sample table below:

Flight      Airport             Datetime
123     AID_X_YZ                5/5/2018 12:52:00 AM
123     AID_X_YZ--> NRT_X       5/6/2018 5:50:00 AM
123     NRT_X                   5/6/2018 7:06:00 AM
123     NRT_X--> SEA            5/7/2018 8:46:00 AM

I would like to display the result from this table like below using SQL server:

Flight  Airport1    Datetime1               Airport2         Datetime2              Airport3    Datetime3           Airport4        Datetime4
123     AID_X_YZ    5/5/2018 12:52:00 AM    AID_X_YZ--> NRT  5/6/2018 5:50:00 AM    NRT_X       5/6/2018 7:06:00 AM NRT_X--> SEA    5/7/2018 8:46:00 AM

Currently, I am using the messy method below in SQL server, text to column in Excel, and some data cleansing to get the result.

Step1:

SELECT DISTINCT
A.FLIGHT
STUFF(ISNULL((SELECT ', ' + X. AIRPORT + ', ' + CONVERT(VARCHAR(20), DATETIME,120) 
FROM #TEMP X WHERE X.FLIGHT = A.FLIGHT 
GROUP BY X.DATETIME, X.AIRPORT
ORDER BY X.DATETIME, X.AIRPORT FOR XML PATH ('')), ''), 1, 2, '') AS DATETIME
FROM #TEMP A 

Step2: Paste the data result into Excel and do a Text to columns (in Delimited) to separate each transaction of Airport and Datetimes like the display desired result as mentioned above.

Step3: Manually cleanup the data and convert the date columns in text back to datetime format.

As you can see, my messy method works but needs a lot of work. Is there a way in SQL server that can be written so it separates the data in Airport and Datetime respectively?

Answer

You can achieve this using dynamic Pivot, try like following query and you will get the desired output. For the simplicity purpose, I have used a temp table, if you want you can avoid it using a inline table.

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
go
--Sample Data
declare @table table(Flight int,      Airport varchar(100),  Datetime datetime)
insert into @table select 123     ,'AID_X_YZ'                ,'5/5/2018 12:52:00 AM'
insert into @table select 123     ,'AID_X_YZ--> NRT_X'       ,'5/6/2018 5:50:00 AM'
insert into @table select 123     ,'NRT_X'                   ,'5/6/2018 7:06:00 AM'
insert into @table select 123     ,'NRT_X--> SEA'            ,'5/7/2018 8:46:00 AM'

--Insert into Temp table and add two column AP and Dt
SELECT flight, 
       airport, 
       [datetime], 
       'Airport' + Cast(Row_number() OVER(partition BY flight ORDER BY [datetime]) AS VARCHAR(100)) ap, 
       'Date'    + Cast(Row_number() OVER(partition BY flight ORDER BY [datetime]) AS VARCHAR(100)) dt 
INTO   #temp 
FROM   @table 



--Generate columns for Airports
DECLARE @colsAirport AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(ap) 
         FROM   #temp 
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 
--Generate columns for Dates
DECLARE @colsDate AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(dt) 
         FROM   #temp 
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 
--Generate select column
DECLARE @colsSelect AS NVARCHAR(max) = Stuff((SELECT DISTINCT 
                                             ', ' + Quotename(ap) + '=' 
                                             + 'MAX(' + Quotename(ap) 
                         + ') , ' + + Quotename(dt) + '=' + 'MAX(' 
                         + Quotename(dt) + ')' 
         FROM   #temp 
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

    DECLARE @query AS NVARCHAR(max) = ' SELECT Flight, ' + @colsSelect + '
                                        FROM   #temp
                                               PIVOT
                                                 (
                                                   MAX(Airport) FOR AP IN (' + @colsAirport + ')' +'
                                                 ) AS pv1
                                                 PIVOT
                                                 (
                                                    MAX([DATETIME]) FOR DT IN (' + @colsDate + ')
                                                 ) AS pv2
                                                 GROUP BY Flight'

    EXECUTE(@query)

Online Demo

Output :

+--------+----------+-------------------------+-------------------+-------------------------+----------+-------------------------+--------------+-------------------------+
| Flight | Airport1 | Date1                   | Airport2          | Date2                   | Airport3 | Date3                   | Airport4     | Date4                   |
+--------+----------+-------------------------+-------------------+-------------------------+----------+-------------------------+--------------+-------------------------+
| 123    | AID_X_YZ | 2018-05-05 00:52:00.000 | AID_X_YZ--> NRT_X | 2018-05-06 05:50:00.000 | NRT_X    | 2018-05-06 07:06:00.000 | NRT_X--> SEA | 2018-05-07 08:46:00.000 |
+--------+----------+-------------------------+-------------------+-------------------------+----------+-------------------------+--------------+-------------------------+