Subquery or CTE to add additional column

Question

I have a large data sets of truck tickets which generate two lines of output per ticket. This is because the ticket has an "out" and "in" component to each ticket. I want to generate one line of output but include information from both the "out" portion of the ticket and the "in" portion.

SELECT Ticket_number, Oil_volume,Faciliy_ID,Ticket_Type
FROM Truckticket T
JOIN TBATTERY TB
ON TB.Battery_ID = T.Battery_ID

My Output has two lines:

Ticket_number	Oil_volume	Facility_ID	Ticket_type
1	10	SK01	O
1	10	SK02	I

Now, what I want my output to be when I use a where clause on Facility_ID SK01:

Ticket_number	Oil_volume	Facility_ID	Facility_ID	Ticket_type
1	10	SK01	SK02	O

I know I have to do this with a subquery or CTE to get Facility_ID SK02 on the same line, but I'm stuck. I hope I have presented my question ok, my first time. Thanks!`

Answer 1

Instead of a subquery or CTE, this looks like a good time to use aggregation and pivoting. With a small number of static types, a simple MAX(CASE... expression with a GROUP BY can pivot the rows to columns.

SELECT
    Ticket_number,
    MAX(CASE WHEN Ticket_Type = 'O' then Oil_volume else NULL END) Oil_volume_out,
    MAX(CASE WHEN Ticket_Type = 'I' then Oil_volume else NULL END) Oil_volume_in,
    MAX(CASE WHEN Ticket_Type = 'O' then Facility_ID else NULL END) Facility_ID_out,
    MAX(CASE WHEN Ticket_Type = 'I' then Facility_ID else NULL END) Facility_ID_in,
FROM Truckticket T
JOIN TBATTERY TB
ON TB.Battery_ID = T.Battery_ID
GROUP BY Ticket_number;