Transformation of records 1 column 3 row -> 1 row 3 column
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
Mode开发者_如何学Pythonller 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.
Take a look on GROUP_CONCAT
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
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
精彩评论