CODEWITHSUNDEEP
phpmysqlsql
kalpaitch
kalpaitch

Reputation: 5271

MySQL, grouping multiple rows where the id is the same

I have inherited a lovely schema which has three fields: id, field_name, field_value. Now you can probably see how this is going to work, for each field that is added via the CMS it is added as a new row, while the id value is NOT unique.

But each field is not an individual entity, they should in fact be grouped by the id (which references the instance of the form that was submitted). As a side note I'd be interested to know what this schema pattern might be called?

//example db contents
1    name        Joe Blogs
1    email       [email protected]
1    programme   sport
2    name        Dave
2    email       [email protected]
2    programme   art

And of course I really want to be using one query to get selected fields grouped by the id field. Output as array( id => array( 'field' => 'value' , 'field' => 'value' ) ) etc

Typically I'd do the below, but I'd like to know if there is a pure SQL way to do this, cutting out the loop and processing at the bottom.

$existing = new stdClass;
$sql = "SELECT 'id','field_name','field_value'
                FROM table_name";
$results = $wpdb->get_results( $sql, ARRAY ); //using wordpress btw
        foreach ( $results as $k=>$r )
            $existing[$r['id']][$k] = $r;

Upvotes: 2

Views: 889

Answers (1)

John Woo
John Woo

Reputation: 263933

You can use aggregate function and case to pivot the values,

SELECT  ID,
        MAX(CASE WHEN type = 'name' THEN value ELSE NULL end) Name,
        MAX(CASE WHEN type = 'email' THEN value ELSE NULL end) email,
        MAX(CASE WHEN type = 'programme' THEN value ELSE NULL end) programme
FROM table1
GROUP BY ID

SQLFiddle Demo

or in the future if you are planning to add another type without altering the query, you can use prepared statements,

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN type = ''',
      type,
      ''' then value ELSE NULL end) AS ',
      type
    )
  ) INTO @sql
FROM table1;

SET @sql = CONCAT('SELECT  ID, ', @sql, ' 
                   FROM table1 
                   GROUP BY ID');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle Demo

Upvotes: 5

Related Questions