[SQL] Join multiple rows from a table in SQL query where the dates and the shifts are the same

I am using this SQL query to collect and analyze shift production data. The result of the query gives me almost what I want but not quite as the infeed, outfeed, and scrap counts are in separate rows. Is there a way to join the rows so that for each shift in a day there is only one row that contains all three values?

Picture of the resulting table

This is what i want the table to look like, something along these lines

Shift tag count date tag2 count2 tag3 count3
C scrap 2284 10/14 outfeed 14862 infeed 17152
B scrap 1693 10/14 outfeed 29117 infeed 30828
A scrap 1498 10/13 outfeed 33891 infeed 35280

Current code:

SELECT shift, tag, "count", TO_CHAR(capturetime, 'MM/DD HH24:MI:SS') AS "Datetime" ,TO_CHAR(capturetime, 'MM/DD') AS "Date"
FROM shift_data.production_counts
where machine = 'SGP3' and tag like '%Reason:Total%' and capturetime >= (CURRENT_TIMESTAMP - '8 days'::interval) and capturetime <= CURRENT_Date
and ((cast(TO_CHAR(capturetime, 'HH24') as int)=6 and cast(TO_CHAR(capturetime, 'MI') as int)>45)
or (cast(TO_CHAR(capturetime, 'HH24') as int)=7 and cast(TO_CHAR(capturetime, 'MI') as int)<1)
or (cast(TO_CHAR(capturetime, 'HH24') as int)=18 and cast(TO_CHAR(capturetime, 'MI') as int)>45)
or (cast(TO_CHAR(capturetime, 'HH24') as int)=19 and cast(TO_CHAR(capturetime, 'MI') as int)<1))
order by capturetime DESC


I know i need a join of some kind but i don't know how to join to the same table if it is doable. Thank you in advance for any help!

Continue reading...