GROUP BY用作列的行上的某个值

Question

I have a table of survey data where lines of results are separated into many rows by, each having their own variable name.

My table looks like this:

data_id  data_content   var_name          var_line
1        1              SERIAL            1
2        2              GND.AGE           1
3        3              GND.NEWS.FREQ     1
4        2              SERIAL            2
5        3              GND.AGE           2
6        3              GND.NEWS.FREQ     2
7        3              SERIAL            3
8        3              GND.AGE           3
9        4              GND.NEWS.FREQ     3

Here is my current query to retrieve the total number of answers for every possible answer of GND.NEWS.FREQ. As in the total number:

SELECT *, COUNT(*) as total
  FROM `data`
 WHERE `var_name` = 'GND.NEWS.FREQ'
 GROUP BY `data_content`

Now I need to add the functionality to only return answers where GND.AGE for example is 3. So basically treat all rows where var_line = 1 as a single row.

I have looked up pivot tables but I'm not sure how to add that into my current query.

I would like to do this in one query if possible but I wouldn't mind doing something like getting var_line IDs in a separate query.

Answer 1

Unfortunately MySQL does not have a PIVOT function which is basically what you are trying to do. So you will need to use an aggregate function with a CASE statement. If you have a unknown number of var_name values that you want to turn into columns, then you can use prepared statements:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when var_name = ''',
      var_name,
      ''' then var_line end) AS ',
      replace(var_name, '.', '_')
    )
  ) INTO @sql
FROM data;

SET @sql = CONCAT('SELECT data_content,', @sql, ' 
                  from data
                  group by data_content
                  ');

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

See SQL Fiddle with Demo