1

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.

Kalyan Vedala
  • 1,049
  • 2
  • 17
  • 33
ale
  • 11,636
  • 27
  • 92
  • 149
  • Just to help the discussion, these operations are typically referred to in the SQL Server world are PIVOT/UNPIVOT. – Stefan Mai Aug 20 '11 at 11:44

2 Answers2

1

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".

Community
  • 1
  • 1
Stefan Mai
  • 23,367
  • 6
  • 55
  • 61
0

You could use a set of case when and column aliases:

Select Case value = 'Low Yield' then 'Low Yield' else NULL END as LowYieldCol,
Case value = 'Yield' then 'Yield' else NULL END as YieldCol,
......

and so on

Hugo Cantacuzene
  • 85
  • 1
  • 7