How to avoid Multiple Tables Union All

Question

I have a table called TblVesselEngineSummary, which has a field called EngineConfiguration containing these values

• 'Single' - Single Engine
• 'Twin' - Double Engine - two records needed
• 'Triple' - Triple Engine - three records needed

In order to achieve it, I have doing union all to same table 3 times. it is impacting the performance, is there a better way to implement this?

I have EngineId as primary key, in order to make each entry unique, I adding 90000000 or 50000000.

SELECT * FROM (

Select
    EngineSummaryID AS EngineId, VesselID_PK AS EngineBoatId,
    
    'Port' AS EngineLocation, EngineHoursPort AS 'Hours', 
    strEngineManufacturer AS 'EngineMake', strEngineModel AS 'EngineModel', 
    strEngineType AS 'EngineType', EngineYear, 
    EngineHP AS 'PowerHP', SerialForPort AS 'SerialNumber', 
    '1' AS 'Sort', UpdatedOn 

from TblVesselEngineSummary 
WHERE IsActiveVesselSummary = 'Y'

UNION ALL
--'Twin','Triple'
Select
    EngineSummaryID + 90000000 AS EngineId, VesselID_PK AS EngineBoatId,
    
    'Starboard' AS EngineLocation, EngineHoursStarboard AS 'Hours', 
    strEngineManufacturer AS 'EngineMake', strEngineModel AS 'EngineModel', 
    strEngineType AS 'EngineType', EngineYear, 
    EngineHP AS 'PowerHP', SerialForPort AS 'SerialNumber', 
    '2' AS 'Sort', UpdatedOn 

from TblVesselEngineSummary 
WHERE IsActiveVesselSummary = 'Y' AND EngineConfiguration IN ('Twin','Triple')

UNION ALL
--'Triple'
Select
    EngineSummaryID + 50000000 AS EngineId, VesselID_PK AS EngineBoatId,
    
    'Starboard' AS EngineLocation, EngineHoursStarboard AS 'Hours', 
    strEngineManufacturer AS 'EngineMake', strEngineModel AS 'EngineModel', 
    strEngineType AS 'EngineType', EngineYear, 
    EngineHP AS 'PowerHP', SerialForPort AS 'SerialNumber', 
    '3' AS 'Sort', UpdatedOn 

from TblVesselEngineSummary 
WHERE IsActiveVesselSummary = 'Y' AND EngineConfiguration = 'Triple'


) allEngines  ORDER BY EngineBoatId, Sort

EngineConfiguration EngineSummaryID VesselID_PK EngineHoursStarboard strEngineManufacturer strEngineModel strEngineType EngineYear EngineHP SerialForPort UpdatedOn
Twin   27092 484405 2825             YANMAR JH57           InBoard  2020 57  5/14/24 3:35 PM
Twin   27090 441067 3351             Yanmar 4JH4-E         InBoard  2006 54  5/10/24 12:52 PM
Single 27080 431834                  MerCruiser 6.2L       InBoard  2008 300  5/7/24 2:43 PM
Twin   27078 495706 1466             Volvo Penta D2-30     InBoard  2019 30   5/7/24 12:58 PM
Triple 27052 496093 125 (Center) 127 YAMAHA 350TXR 350 TUR Outboard 2008 350 4/10/24 4:21 PM

Answer 1

You can take the variable parts of your query and wrap them up in a cross apply, which can generate multiple variations in the data and can also apply the necessary conditionals.

Something like:

SELECT E.*, VES.*
FROM TblVesselEngineSummary VES
CROSS APPLY (
    SELECT
        VES.EngineSummaryID AS EngineId,
        'Port' AS EngineLocation,
        VES.EngineHoursPort AS Hours,
        1 AS 'Sort'
    UNION ALL
    SELECT
        VES.EngineSummaryID + 90000000 AS EngineId,
        'Starboard' AS EngineLocation,
        VES.EngineHoursStarboard AS Hours,
        2 AS 'Sort'
    WHERE VES.EngineConfiguration IN ('Twin','Triple')
    UNION ALL
    SELECT
        VES.EngineSummaryID + 50000000 AS EngineId,
        'Starboard' AS EngineLocation,
        VES.EngineHoursStarboard AS Hours,
        3 AS 'Sort'
    WHERE VES.EngineConfiguration = 'Triple'
) E
WHERE VES.IsActiveVesselSummary = 'Y'
ORDER BY VES.VesselID_PK, E.Sort

A CROSS APPLY is like an INNER JOIN to a subselect, also sometimes called a lateral join. For each row from the TblVesselEngineSummary table, the cross apply above will construct one to three sets of data that are effectively joined to the initial table. Note that the subselect accesses the outer TblVesselEngineSummary table reference, so data is retrieved only once.

(I'll let you work out the details for the final select list.)

Sample results (with some minimal fabricated test data):

EngineId	EngineLocation	Hours	Sort	VesselID_PK	EngineSummaryID	IsActiveVesselSummary	EngineConfiguration	EngineHoursPort	EngineHoursStarboard
111	Port	1000	1	1	111	Y	Single	1000	0
222	Port	2200	1	2	222	Y	Twin	2200	2250
90000222	Starboard	2250	2	2	222	Y	Twin	2200	2250
333	Port	3300	1	3	333	Y	Triple	3300	3350
90000333	Starboard	3350	2	3	333	Y	Triple	3300	3350
50000333	Starboard	3350	3	3	333	Y	Triple	3300	3350

See this db<>fiddle for a demo.

Should SerialNumber also be variable? All three subqueries reference SerialForPort. Is there a SerialForStarboard?