SQL to convert multiple rows into a single row of variable length

Question

I am writing a module for drupal6/PHP5 using MySQL (and optionally PostgreSQL).

To represent arbitrary sample properties and instrument readings in an experiment, I have two tables: one of fields which names the properties and one of parameters which holds the property value for each reading. A parameter will only have a numeric value (FLOAT/REAL) or a string value (TEXT), but not both (according to the field's type, which is in the Fields table).

CREATE TABLE Fields (
    `fid` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'field ID',
    `name` VARCHAR(128) NOT NULL,
    `type` ENUM('float', 'real', 'text') NOT NULL
    -- ...
);

CREATE TABLE Params (
    `pid` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'param ID', 
    `lid` INT UNSIGNED NOT NULL,
    `fid` INT UNSIGNED NOT NULL COMMENT 'field ID',
    `numVal` FLOAT,
    `stringVal` VARCHAR(256),
    FOREIGN KEY (`fid`) REFERENCES Fields (`fid`)
); 

For a given lid there are multiple/variable numbers of parameters/rows which I can easily retrieve as rows in a loop. How can I retrieve a single row of values, where each column has the associated field name as its heading, for each lid? In Drupal such a query would return all field:value pairs as object attribute:value pairs in a single database call, which I figure would be more efficient.

By way of example, take the following sample data:

INSERT INTO Fields (`fid`, `name`, `type`)
  VALUES
(1, 'voltage', 'float'),
(2, 'current', 'float'),
(3, 'size', 'float'),
(4, 'colour', 'text')
;

INSERT INTO Params (pid, lid, fid, numVal, stringVal)
  VALUES
(1, 1, 1, 2.5, NULL),
(2, 1, 4, NULL, 'blue'),
(3, 2, 1, 2.7, NULL),
(4, 2, 2, 13, NULL),
(5, 2, 3, 27, NULL),
(6, 2, 4, NULL, 'greenish blue')
;

For LID=1, the result should be a single row:

voltage	colour
2.5	'blue'

For LID=2:

voltage	current	size	colour
2.7	13	27	'greenish blue'

Google has found me a few SQL examples "combining multiple rows into a single row", but they seem to assume pre-ordained labelling of a fixed number of columns/fields in the result set, and/or build column entries as single composite "name:value" strings. I'm hoping there might be a more general approach for arbitrary numbers of fields, that works for both MySQL and PostgreSQL.

I only have very basic SQL experience, so I don't know whats possible regarding views or temporary tables or defining and executing procedures on the fly.

Answer 1

This displays the data vertically rather than horizontally.

SELECT
    FIELDS.NAME AS PROPERTYNAME,
    CASE WHEN PARAMS.NUMVAL IS NULL THEN STRINGVAL ELSE CAST(PARAMS.NUMVAL AS VARCHAR(100)) END AS PROPERTYVALUE
FROM
    PARAMS, FIELDS
WHERE
    PARAMS.FID = FIELDS.FID
        AND
    PARAMS.LID = 1
ORDER BY
    PARAMS.FID

The SQL may need some tweaking, but it is the best I can do without access to your DB schema.

Answer 2

Pivot table stuff seems just too messy. I am thinking to just go with something simple like this (Thanks John Pick for showing me how to use CASE!):

SELECT p.l_id, 
   GROUP_CONCAT(
       CONCAT(f.name,'=', 
          CASE WHEN p.stringval IS NULL
               THEN p.numval 
            ELSE p.stringval 
          END
       ) SEPARATOR '||'
   ) AS vals 
 FROM
    params AS p 
 LEFT JOIN fields AS f 
    ON  p.f_id=f.f_id 
 WHERE 
    p.l_id=1 
 GROUP BY 
    p.l_id;

and just post-process the "vals" string with explode() in PHP.

+------+---------------------------------------------------------- ... -+
| l_id | vals                                                           |
+------+---------------------------------------------------------- ... -+
|    1 | kv=50||ma=50||pressure=0||temp_ccd=-58||temp_sample=150|| ...  |
+------+---------------------------------------------------------  ...  +

What a cop out! I would've expected a much more elegant solution given that all the information required to return columns in correct format exists in the PARAMS AND FIELDS tables. And now I have to worry about stringvals with SEPARATORs in them :-/

Answer 3

The difficulty in some RDBMSs with pivoting a variable number of columns shows why EAV is problematic in a relational database. It's an attempt to shoehorn a different object model into the relational model. In these cases, it can be simpler to perform some of the data operations on the programming side (here, PHP). For the DB side, do as much as makes sense with the relational model:

SELECT p.lid, 
       f.name,
       CASE f.type 
         WHEN 'text' THEN p.stringVal 
         ELSE p.numVal 
       END AS value
  FROM Fields AS f
    JOIN Params AS p ON f.fid = p.fid
  WHERE p.lid = ?

Then, take these results and combine them (in PHP):

$stmtReadings = <<<'EOS'
  SELECT p.lid, f.name,
         -- …
EOS;

$sample = [];
$qryReadings = $db->prepare($stmtReadings);
foreach ($qryReadings->execute([$lid]) as $reading) {
    $sample[$reading['name']] = $reading['value'];
}

For multiple samples, the prepared statement could be executed multiple times (once for each sample). For all the samples, it's likely more performant to fetch all params in a single query, and combine them in a single loop.

$stmtReadings = <<<'EOS'
  SELECT p.lid, 
         f.name,
         CASE f.type 
           WHEN 'text' THEN p.stringVal 
           ELSE p.numVal 
         END AS value
    FROM Fields AS f
      JOIN Params AS p ON f.fid = p.fid
    ORDER BY p.lid
EOS;

$samples = [];
foreach ($db->query($stmtReadings) as list($lid, $name, $value)) {
    $samples[$lid][$name] = $value;
}

Note the above requires the query columns have a specific order. As of PHP 7.1, destructuring assignment can be used for a more robust program:

// …
foreach ($db->query($stmtReadings) as ['lid' => $lid, 'name' => $name, 'value' => $value]) {
    $samples[$lid][$name] = $value;
}