I am getting records from different tables and concatenating the values like this so that I can display Flight1, Flight2, Flight3, and Flight4...... as columns. Since I am using the same conditions, Is there any way to simplify this query?
SELECT b.BookingID
,(SELECT TOP 1 CONCAT(il.CCode,il.FlightNumber,';',dc.[Name])
FROM ILeg il
INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
INNER JOIN City dc ON dc.CityCode = il.DepartureStation
INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
WHERE bp.BookingID = b.BookingID AND il.CarrierCode = '12T'
ORDER BY il.STD
) AS Flight1
,(SELECT CONCAT((il.CCode,il.FlightNumber,';',dc.[Name]))
FROM ILeg il
INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
INNER JOIN City dc ON dc.CityCode = il.DepartureStation
INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
WHERE bp.BookingID = b.BookingID AND il.CarrierCode = '12T'
ORDER BY il.STD
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY) AS Flight2
,(SELECT CONCAT((il.CCode,il.FlightNumber,';',dc.[Name]))
FROM ILeg il
INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
INNER JOIN City dc ON dc.CityCode = il.DepartureStation
INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
WHERE bp.BookingID = b.BookingID AND il.CarrierCode = '12T'
ORDER BY il.STD
OFFSET 2 ROWS
FETCH NEXT 1 ROWS ONLY) AS Flight3
FROM Booking b
WHERE b.RecordLocator='1234'
Thank you
CodePudding user response:
You can use a CTE on top and the main query might look as below.
;with CTE as
(SELECT CONCAT((il.CCode,il.FlightNumber,';',dc.[Name])) as DisplayName
FROM ILeg il
INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
INNER JOIN City dc ON dc.CityCode = il.DepartureStation
INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
WHERE bp.BookingID = b.BookingID AND il.CarrierCode = '12T'
ORDER BY il.STD),
SELECT b.BookingID
,(SELECT TOP 1 * from CTE) AS Flight1
,(SELECT TOP 1 * from CTE OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS Flight2
,(SELECT TOP 1 * from CTE OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) AS Flight3
FROM Booking b
WHERE b.RecordLocator='1234'