Sub-query with server-side prepared statements?

Question

I'm working with a weird database schema : the data I'm looking for is stored in one of 30 columns : value1, value2, value3 ... value30.

Using a query, I know which column number to look up:

enter image description here

How can I use this number to tell mysql to bring me, for each "machine_id", the corresponding "valuex" column ?

I've read MySQL concat() to create column names to be used in a query? , should I apply that technique or look elsewhere ?

It would be better to fix the schema than to pander to it by working out how to deal with it. It is going to be a nightmare, all the time. — Jonathan Leffler, Apr 23 '12 at 13:51
The client wants something that's dynamic. Each machine can have up to 30 different values, and they could be in different orders. So that's what we came up with :| — Manu, Apr 23 '12 at 13:53
at first we had something completly dynamic, we could have any number of values for each machine type. But importing data into that was too slow. — Manu, Apr 23 '12 at 13:55
The client is clueless. What are the '30 different values'? The different value types (IP address, memory, disk space, operating system version) should each be stored in its own named column. The claim of dynamic simply is anarchic or even heretical, and makes life hell for developers. Get out while the going is good and you have some sanity left. — Jonathan Leffler, Apr 23 '12 at 13:59
I get the data from the machines through a box, and depending on the type/brand of the box some values are returned while others are not. yes, it's not pretty. — Manu, Apr 23 '12 at 14:13

score 0 · Accepted Answer · answered Apr 23 '12 at 13:37

0

If you have exactly 30 fields (table schema is not changing), then you can use simple SQL query -

SELECT
  machine_id,
  SUM(IF(where_is_data = 1, value1, NULL)) AS value1,
  SUM(IF(where_is_data = 2, value2, NULL)) AS value2,
  SUM(IF(where_is_data = 3, value3, NULL)) AS value3,
  ...
  SUM(IF(where_is_data = 30, value30, NULL)) AS value30
FROM
  table
GROUP BY
  machine_id;

answered Apr 23 '12 at 13:37

Devart

119,203
23
166
186

Won't that create 30 "valueX" ? I would still need to know which one to display – Manu Apr 23 '12 at 13:40

score 0 · Answer 2 · answered Apr 23 '12 at 14:14

I did it with a simple CASE :

SELECT
    inverter.id as inverter_id,
    SUM(CASE
        when csvData.value = 1 then data.value1
        when csvData.value = 2 then data.value2
        when csvData.value = 3 then data.value3
        (...)
        when csvData.value = 30 then data.value30   
    END)
    as value

FROM
    (...)

Sub-query with server-side prepared statements?

2 Answers2