Display multiple values from Database in one column

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.
Like:

PieceType
CTN

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


Source: mysql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.