I am trying to create a report that will display different information from the database. In my report I need to display the column PieceType from the table job_pieces. But each company could have a different number of PieceTypes for example Company 135 only has type PLT but company 99 has PLT, CASE, CTN etc
In my report I want to display each PieceType like a column. Because if I just put
jp.PieceType in the select then it will only display one value even though there could be 5 different pieces.
But it should be like:
PieceType CTN CASE PLT
My SQL query:
SELECT c.Name, jn.CompanyID, COUNT(distinct jn.ID) as Jobs, SUM(jn.ActualWeight) as GrossWt, (select COUNT(job_debriefs.ID) from job_debriefs WHERE JobID = jn.JobNo) as Debriefs, sum(jn.OutTurn) as Outturn, jp.PieceType FROM customer c LEFT JOIN job_Address ja ON c.AccountCode = ja.Code AND c.Company_ID = ja.Company_ID LEFT JOIN job_new jn ON ja.JobID = jn.ID LEFT JOIN job_pieces jp ON ja.JobID = jp.ID WHERE c.Company_ID = ?compID GROUP BY c.ID
So you can see from the SQL query I have the columns Name, CompanyID, Jobs etc. I want the piece types to be displays in the row like a column to, so after Outturn it could be PLT, CASE etc depending on the company. I need to display all the PieceType values for each company but I won’t know how many each company has. Right now my sql query only displays one PieceType