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?
Advertisement
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)
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 |
+--------+----------+-------------------------+-------------------+-------------------------+----------+-------------------------+--------------+-------------------------+