I cannot resist pitching in another method using a Tally type cross-tab, which performs at least as good as the method Jeff posted, although it doesn't result in a parallel plan. The difference isn't great on smaller and simpler sets, somewhat greater on larger and more complex sets.
😎
Simple sample set and the two methods
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL
,Aircraft1 VARCHAR(50) NOT NULL
,Aircraft2 VARCHAR(50) NOT NULL
,Aircraft3 VARCHAR(50) NOT NULL
,City1 VARCHAR(50) NOT NULL
,City2 VARCHAR(50) NOT NULL
,City3 VARCHAR(50) NOT NULL
)
INSERT INTO dbo.TBL_SAMPLE_DATA
(
Airline
,Aircraft1
,Aircraft2
,Aircraft3
,City1
,City2
,City3
)
VALUES
('AZ','A319','A320','A330','FCO','LIN','MXP')
,('BA','A380','B747','A320','LHR','LGW','LCY')
,('DL','B777','A330','B757','DTW','BOS','LAX')
,('AA','B767','B777','A319','DFW','JFK','ORD')
,('NW','A320','B757','MD90','MSP','SLC','SEA')
,('KL','B747','A380','B737','AMS','CDG','TPE')
;
RAISERROR('CROSS APPLY UNION',1,1) WITH NOWAIT;
SET STATISTICS IO, TIME ON;
SELECT s.Airline, d.Aircraft, d.City
FROM dbo.TBL_SAMPLE_DATA s
CROSS APPLY (
SELECT Aircraft1, City1 UNION ALL
SELECT Aircraft2, City2 UNION ALL
SELECT Aircraft3, City3
) d (Aircraft, City);
SET STATISTICS TIME, IO OFF;
RAISERROR('TALLY CASE',1,1) WITH NOWAIT;
SET STATISTICS IO, TIME ON;
;WITH NUMA(N) AS (SELECT N FROM (VALUES (1),(2),(3))AS X(N))
SELECT
SD.Airline
,CASE
WHEN NA.N = 1 THEN SD.Aircraft1
WHEN NA.N = 2 THEN SD.Aircraft2
WHEN NA.N = 3 THEN SD.Aircraft3
END AS Aircraft
,CASE
WHEN NA.N = 1 THEN SD.City1
WHEN NA.N = 2 THEN SD.City2
WHEN NA.N = 3 THEN SD.City3
END AS City
FROM dbo.TBL_SAMPLE_DATA SD
CROSS JOIN NUMA NA
SET STATISTICS TIME, IO OFF;
Output
CROSS APPLY UNION
Msg 50000, Level 1, State 1
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 21 ms.
Table 'TBL_SAMPLE_DATA'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
TALLY CASE
Msg 50000, Level 1, State 1
SQL Server parse and compile time:
CPU time = 4 ms, elapsed time = 4 ms.
Table 'TBL_SAMPLE_DATA'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
A test set generator
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(10) NOT NULL
,Aircraft1 VARCHAR(10) NOT NULL
,Aircraft2 VARCHAR(10) NOT NULL
,Aircraft3 VARCHAR(10) NOT NULL
,City1 VARCHAR(10) NOT NULL
,City2 VARCHAR(10) NOT NULL
,City3 VARCHAR(10) NOT NULL
)
DECLARE @SAMPLE_SIZE INT = 10000000;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,BASE_SAMPLE AS
(
SELECT
STUFF(REPLICATE('0',LEN(@SAMPLE_SIZE) + 1),(LEN(@SAMPLE_SIZE) + 2) - LEN(NM.N),LEN(NM.N),CAST(NM.N AS VARCHAR(10))) AS KEY_COLUMN
,REPLACE(NEWID(),'-','X') AS SAMPLE_STRING
FROM NUMS NM
)
INSERT INTO dbo.TBL_SAMPLE_DATA
(
Airline
,Aircraft1
,Aircraft2
,Aircraft3
,City1
,City2
,City3
)
SELECT
BS.KEY_COLUMN
,SUBSTRING(BS.SAMPLE_STRING, 1, 3)
,SUBSTRING(BS.SAMPLE_STRING, 4, 3)
,SUBSTRING(BS.SAMPLE_STRING, 7, 3)
,SUBSTRING(BS.SAMPLE_STRING,10, 3)
,SUBSTRING(BS.SAMPLE_STRING,13, 3)
,SUBSTRING(BS.SAMPLE_STRING,16, 3)
FROM BASE_SAMPLE BS;