CODEWITHSUNDEEP
mysqlsqlselect
user123456789
user123456789

Reputation: 2004

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

Upvotes: 0

Views: 860

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

The aggregate function to make values from different rows a concatenated string in MySQL is GROUP_CONCAT.

SELECT  c.Name,
        ...
        GROUP_CONCAT(DISTINCT jp.PieceType)
FROM customer c
...
GROUP BY c.ID;

You can also specify the delimiter and an order by clause. More Information here: dev.mysql.com/...function_group-concat.

Upvotes: 1

Related Questions