Display multiple values from Database in one column

Question

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

The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. — jarlh
– jarlh, Commented Jul 7, 2015 at 8:49
Are you simply looking for GROUP_CONCAT(DISTINCT jp.PieceType)? — Thorsten Kettner
– Thorsten Kettner, Commented Jul 7, 2015 at 8:56
I haven't read the whole question, but I'd try GROUP BY c.Name, jn.CompanyID, jp.PieceType. Perhaps also jn.JobNo. — jarlh
– jarlh, Commented Jul 7, 2015 at 8:59
@user123456789: Okay, so I'll make this an answer, so future readers can find the solution easily. — Thorsten Kettner
– Thorsten Kettner, Commented Jul 7, 2015 at 10:31

Thorsten Kettner · Accepted Answer · 2015-07-07 10:35:53Z

1

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.

answered Jul 7, 2015 at 10:35

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Display multiple values from Database in one column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related