ale ale - 7 months ago 11
SQL Question

Improving a basic SQL query - grouping results

I am improving a piece of PHP code that executes a MySQL query. The query currently produces a set of results like this:

id summary status date_submitted last_updated value
48 test case 1 30 1313755157 1313755252 Low Yield
48 test case 1 30 1313755157 1313755252 28BK
48 test case 1 30 1313755157 1313755252 Yield
48 test case 1 30 1313755157 1313755252 3
48 test case 1 30 1313755157 1313755252 1
48 test case 1 30 1313755157 1313755252 n/a


Value
will always be different but all the other fields will be the same value. I want to transform the above to look like this

id summary status date_submitted last_updated value1, value2, value3, value4, value5
48 test case 1 30 1313755157 1313755252 Low Yield 28BK Yield 3 1 n/a


so each of the values have their own column too. I don't think I need to paste the massive query that gets the first result? I guess people can treat the result as a table and come up with a query from that? I'll post the original query if requested.

Thanks very much.

Answer

You can't do exactly that, but you can do a comma separated list of values. See this question:

How to use GROUP BY to concatenate strings in MySQL?

You would write something like:

SELECT
  id,
  summary,
  status,
  date_submitted,
  last_updated,
  GROUP_CONCAT(value SEPARATOR ',') as values
FROM
  table
GROUP BY
  id,
  summary,
  status,
  date_submitted,
  last_updated

In this case, values would take on the value "Low Yield,28BK,Yield,3,1,n/a".

Comments