Transformation of records 1 column 3 row -> 1 row 3 column

Question

First look at below query

SELECT COUNT(id) AS total_record, id, modeller,
  MONTHNAME(completed_date) AS current_month,
  QUARTER(completed_date) AS current_quarter,
  Difficulty,
  YEAR(completed_date) AS current_year
FROM model
WHERE modeller != ''
  AND completed_date BETWEEN '2010-04-01'
  AND '2010-05-31' AND Difficulty != ''
GROUP BY Difficulty,
MONTH(completed_date) ORDER BY
MONTH(completed_date) ASC

Results I am getting is

Modeller  Month  Year  Difficulty

XYZ       Jan    2010  23

XYZ       Jan    2010  14

XYZ       Jan    2010  15

ABC       Feb    2010   5

ABC       Feb    2010  14

ABC       Feb    2010   6

I want result like

Modeller  Month  Year  Difficulty

XYZ       Jan    2010  23, 14, 15

ABC       Feb    2010  5, 14, 6

My database is Mysql for application i am developing so any help would be greatly appericated.

Thanks,

Answer 1

Take a look on GROUP_CONCAT

Answer 2

I think you want GROUP_CONCAT(). I've simplified your fields so you'll need to add the calculations back in, but something like this should do:

SELECT modeller, month, year, GROUP_CONCAT(DISTINCT Difficulty)
FROM Model
WHERE $conditions
GROUP BY modeller, month, year

Answer 3

Nice, the GROUP_CONCAT function is powerfull stuff, and probably is a perfect solution in this case.

But watch out, in big systems this kind of manipulation can belong in the application layer and not in the database queries, maximizing easy of maintenance, re-utilization and reducing dependency in the specific RDBMS.

An alternative (more engineered) wait it to use the DAO pattern. Like documented in http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html