Shifting mysql database values from a couple of columns to rows of entries

Question

Easier to describe by showing a simplified view of the existing data structure and the desired result...

---

CURRENTLY...

Element       Response           ElementType    ElementNumber

EntryVal.1    1234.56            EntryVal       1
EntryDes.1    'Current Value'    EntryDes       1

EntryVal.2    4321.0             EntryVal       2
EntryDes.2    'Another Value'    EntryDes       2

EntryVal.3    6543.21            EntryVal       3
EntryDes.3    'Final Value'      EntryDes       3

---

DESIRED...

Name           Value

Current Value  1234.56
Another Value  4321.0
Final Value    6543.21

---

(split element column into ElementType and ElementNumber column in the hopes it might help)

Have tried various sub-selects but have not found the secret.

Could do some looping in PHP but hope there is a more elegant sole single MySQL query approach.

There is other columns like location involved so trying to keep it clean.

Answer 1

Use:

  SELECT MAX(CASE WHEN t.elementtype = 'EntryDes' THEN t.response END) AS Name,
         MAX(CASE WHEN t.elementtype = 'EntryVal' THEN t.response END) AS Value,
    FROM YOUR_TABLE t
GROUP BY t.elementnumber

You might want to keep elementnumber as a column, in case you need to ensure order.

Answer 2

Here's how I'd do it:

SELECT des.Response AS Name, val.Response AS Value
FROM MyTable AS des JOIN MyTable AS val USING (ElementNumber)
WHERE des.ElementType = 'EntryDes' AND val.ElementType = 'EntryVal';