columns in SQL Server

Question

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 1

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 |
+--------+----------+-------------------------+-------------------+-------------------------+----------+-------------------------+--------------+-------------------------+

Answer 2

Assuming you always have four points of data for each flight, then you could try doing a pivot with the help of ROW_NUMBER:

WITH cte AS (
    SELECT Flight, Airport, Datetime
        ROW_NUMBER() OVER (PARTITION BY Flight ORDER BY Datetime) rn
    FROM yourTable
)

SELECT
    Flight,
    MAX(CASE WHEN rn = 1 THEN Airport END) AS Airport1,
    MAX(CASE WHEN rn = 1 THEN Datetime END) AS Datetime1,
    MAX(CASE WHEN rn = 2 THEN Airport END) AS Airport2,
    MAX(CASE WHEN rn = 2 THEN Datetime END) AS Datetime2,
    MAX(CASE WHEN rn = 3 THEN Airport END) AS Airport3,
    MAX(CASE WHEN rn = 3 THEN Datetime END) AS Datetime3,
    MAX(CASE WHEN rn = 4 THEN Airport END) AS Airport4,
    MAX(CASE WHEN rn = 4 THEN Datetime END) AS Datetime4
FROM cte
GROUP BY
    Flight;