0

Though there are a number of questions on pivoting tables, none of them seemed to address what I need to do or seem too complex and I could not get to work.

I have an app that collects data from remote telematics devices over wireless networks (e.g. GSM modems running various embedded data collection apps). My database associates each set of data with a "Modem ID". There are numerous parameters that are collected (about 100 different types, thus it would not seem practical to create a table with 100 columns for each modem, particularly as different modem apps may only use 10 of the possible 100 parameters). Thus I have a very simple 'parameter' table with fields 'modem_id', 'parm_id', and 'value' -- i.e. a classic key=value table.

I would like to write a simple SQL query that will allow me to search for specific modems, sort on a particular parm_id, and paginate results using limit and offset.

An example of some rows in Table parameter:

modem_id | parm_id | value
100 1 '11.1'
100 4 '22.2'
100 9 '3.33'
102 1 '12.3'
102 4 '23.4'
102 9 '3.45'

I would like to group the above by modem_id into a temp table with the parm_ids in separate columns, e.g.:

modem_id | parm_id1 | parm_id4 | parm_id9
100 '11.1' '22.2' '3.33'
102 '12.3' '23.4' '3.45'

This seems like it ought to be simple, and like it would be a very common thing, but the only solutions I've seen involve dozens of lines of code, prepared statements, and strange escape sequences with lots of quote characters everywhere that leave me unable to even understand what the query is supposed to do and which I have been unable to get to work for this example.

David G
  • 106
  • 6
  • Don't you have any dates? Every parameter has a single value for each modem? Do you need to aggregate any parameter values? – geoandri Oct 26 '14 at 05:51
  • @geoandri Yes, there is only one value for each modem_id, parm_id combination. There is also a timestamp field in the parameter table but that does not need to be included in the solution as it is usually not used in the top level query and can be retrieved separately if needed (and I wanted to keep the question as simple as possible). And I do not need to aggregate any values. – David G Oct 26 '14 at 06:03

1 Answers1

0

The following query will get you the result you need.

 select  modem_id, 
 IF(parm_id = 1, value, NULL) as parm_id1,
 IF(parm_id = 2, value, NULL) as parm_id2,
 ......
 IF(parm_id = 100, value, NULL) as parm_id100,
  from table

Hope it helps.

geoandri
  • 2,360
  • 2
  • 15
  • 28
  • This looks like it should work well, and actually may not be too lengthy for < ~20 parm_ids, and is easy to generate in PHP. Will test in the next day or 2 and then update this comment with the results. Thanks – David G Oct 26 '14 at 07:09
  • @davidg 'generate in PHP' ???? If you're using PHP, don't bother with the pivot. Far, far simpler, more scalable, and more flexible to do this with a loop – Strawberry Oct 26 '14 at 08:28
  • @Strawberry Yes for small data sets it would be much easier in PHP, and I have been doing it that way so far. But the DB is getting larger with thousands of modems (probably 10,000 in 1-2 years) and I want to now be able to sort and paginate the results in SQL rather than have to read 100K+ rows of data in to PHP which would increase server bandwidth and reduce performance. Also as this seems like such a common thing, i.e. converting a 1:n relationship between modem and parameter table to more like a 1:1 relationship, I was surprised that there was not a straightforward way to do it in MySQL. – David G Oct 26 '14 at 20:59
  • @geoandri This is a straightforward approach that would 'pivot' the table, however it has a major performance limitation in that it is not dynamic with respect to populating only columns that are present in the actual data set. e.g., in my example only parm_id's 1, 4 and 9 are in the data set, so I don't want the query to populate 100 columns when it should only populate 3 columns in this case. Let me know if you can think of a more optimal solution that will only populate the columns present in the data set. Thanks – David G Oct 26 '14 at 21:09
  • @DavidG With your existing approach, have you actually encountered a performance hit? – Strawberry Oct 26 '14 at 22:38
  • @Strawberry Not yet but only because the table is still small. I'm now implementing pagination, and would like to create a long-term solution that will minimize DB server bandwidth (otherwise could be 1MB of traffic to the DB server every time) by doing sort and paginate in the DB. BTW looks like I'm now going divide & conquer by first retrieving only the 1 parm_id of interest, sort and paginate in sql, then in a 2nd request I will get all other associated parms and merge in php. This should work very well, but if there's a more elegant way to do in a single sql query that would be cool too. – David G Oct 27 '14 at 02:00
  • I'm still not convinced by any of this, but I guess you've done your research! – Strawberry Oct 27 '14 at 07:32
  • 1
    @DavidG I think in the case that you don't know which are the parameters that you are going to populate the following link will help you http://stackoverflow.com/questions/10925445/mysql-select-dynamic-row-values-as-column-names-another-column-as-value – geoandri Oct 27 '14 at 09:22
  • @geoandri Looks like `GROUP CONCAT` should do exactly what I need, thanks for the link – David G Oct 28 '14 at 00:02