union three select queries with where clause into one datatable

Question

I have a query that has a where clause with three RouteIDs so there are 3 rows returns each time as opposed to one.

SELECT  
    A.ItemId, A.RefId, B.EquipmentName, C.PointId, C.PointDescription,
    D.InspectionTimeStamp, D.AlarmCode, D.AlarmDescription,
    A.RouteId, A.Type, A.SequenceNo, A.Skip, A.Item  
FROM 
    [Trident].[Maintenance].[BasicCareEquipmentPointDetails] A 
INNER JOIN 
    [Trident].[Maintenance].[Equipment] B ON A.ItemId = B.EquipmentId 
INNER JOIN 
    [Trident].[Maintenance].[BasicCarePointDetails] C ON A.RouteId = C.RouteId 
LEFT OUTER JOIN 
    [Trident].[Maintenance].[BasicCareInspectionHistory] D ON C.PointId = D.PointId 
                            AND D.InspectionTimeStamp BETWEEN '02/18/2021' AND DATEADD(DAY, 1, '02/18/2021') 
WHERE 
    A.RouteId IN ('RG00000792', 'RG00000800', 'RG00000801')  
    AND A.Skip = 0 
    AND A.ItemId = C.TemplateId 
ORDER BY  
    A.RefId, A.SequenceNo DESC

This the result of the query, 3 rows for each routeID, would like to capture the alarmCode for each of these routes and any other column I may need and change the column name to an alias all on one row. So that I have all three routes in one row so Active Reports can iterate through the data source with issues.

I include screenshot of the report I'm trying to get, each one of those shifts is a routeID. As you can see there each has a FILTER/COND STRIPPER which falls under the left most column and a sub row below "Flush Filters" the alarmCode would be the Ok in each of the route columns.

I've tried to use union and forums do not recommend it. At this point I can make three SQL calls in vb.net code and create the desired formatted table and just iterate through each table and add to the custom table but I feel there is a better way. I am using SQL Server 2017 (v14.0.3281.6).

Answer 1

i believe i found a solution not sure if its the easiest to implement in vb.net code, ill have to call the temp table and usually has a long winded name auto generated. #TEMP_____________231651651 for example is one i got back for another application. but anywho this is what we came up with

Go
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP Table #Data
SELECT A.ItemId ,A.RefId ,B.EquipmentName ,C.PointId ,C.PointDescription, 
D.InspectionTimeStamp ,D.AlarmCode,D.AlarmDescription ,A.RouteId ,A.Type ,A.SequenceNo 
,A.Skip ,A.Item INTO #Data FROM [Trident].[Maintenance]. 
[BasicCareEquipmentPointDetails] A INNER JOIN [Trident].[Maintenance].[Equipment] B ON 
A.ItemId = B.EquipmentId INNER JOIN [Trident].[Maintenance].[BasicCarePointDetails] C 
ON A.RouteId = C.RouteId LEFT OUTER JOIN [Trident].[Maintenance]. 
[BasicCareInspectionHistory] D ON C.PointId = D.PointId AND D.InspectionTimeStamp 
BETWEEN '03/06/2020' AND DATEADD(DAY, 1, '03/06/2020') WHERE A.RouteId In 
('RG00000792', 'RG00000800', 'RG00000801')  AND A.Skip = 0 AND A.ItemId = 
C.TemplateId ORDER BY  A.RefId, A.SequenceNo Desc 

SELECT *
FROM (SELECT ItemId
 ,RefId
 ,EquipmentName
 ,PointId
 ,PointDescription
 ,InspectionTimeStamp
 ,AlarmCode
 ,AlarmDescription
 ,RouteId
 ,type
 ,SequenceNo
 ,Skip
 ,Item FROM #Data) AS s
PIVOT (
 MAX(AlarmCode)
 FOR RouteId IN ([RG00000792], [RG00000800], [RG00000801])
) p
ORDER BY RefId, SequenceNo DESC

Using the pivot feature. not sure if aggregating the AlarmCode column is the cleanest though, even though its not doing a max on a string value