MySQL Pivot/Crosstab Query

mysql pivot/crosstab query

While @John's static answer works great, if you have an unknown number of columns that you want to transform, I would consider using prepared statements to get the results:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT((CASE node_id when ',
      node_id,
      ' then entered_value else NULL END)) AS user_input',
      node_id
    )
  ) INTO @sql
FROM trn_user_log;


SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, ' 
                  FROM trn_user_log 
                  GROUP BY app_id, transaction_id, mobile_no');

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

see SQL Fiddle with Demo

As far as your second, please clarify what you are trying to do it is not clear.

Is it possible to use Crosstab/Pivot Query in MySQL?

You can use this query -

SELECT
  supplier_id,
  MAX(IF(date = '2012-01-01', value, NULL)) AS '2012-01-01',
  MAX(IF(date = '2012-01-03', value, NULL)) AS '2012-01-03',
  MAX(IF(date = '2012-05-01', value, NULL)) AS '2012-05-01'
FROM (
  SELECT supplier_id, DATE(date) date, CONCAT(SUM(price), '(', qty, ')') value FROM supplier
    GROUP BY supplier_id, DATE(date)
    ) t
  GROUP BY supplier_id;

+-------------+------------+------------+------------+
| supplier_id | 2012-01-01 | 2012-01-03 | 2012-05-01 |
+-------------+------------+------------+------------+
|           1 | 500.00(2)  | 450.00(10) | NULL       |
|           2 | 400.00(5)  | NULL       | NULL       |
|           3 | NULL       | NULL       | 500.00(1)  |
+-------------+------------+------------+------------+

It produces result you want. But if you want to do it dynamically, then have a look at this article 'Automate pivot table queries' - http://www.artfulsoftware.com/infotree/queries.php#523, or this link - Dynamic pivot tables.

MySQL CROSSTAB or PIVOT Query

Simple answer, but I've just had one cup of coffee yet...

select 'answer1' as questions,
       sum(case when answer1 = 1 then 1 end) as total_1,
       sum(case when answer1 = 0 then 1 end) as total_0,
       sum(case when answer1 = NA then 1 end) as total_NA
from tablename
UNION ALL
select 'answer2' as questions,
       sum(case when answer2 = 1 then 1 end) as total_1,
       sum(case when answer2 = 0 then 1 end) as total_0,
       sum(case when answer2 = NA then 1 end) as total_NA
from tablename
etc...

MySQL pivot / crosstab

You can write your dynamic pivot query as below

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
  CONCAT('MAX(CASE WHEN channel_code = ''',
         channel_code, 
         ''' THEN value  END) `',
         channel_code,'`'
         )
          ORDER BY date_time,channel_code ASC        
 )
  INTO @sql
  FROM test;

SET @sql = CONCAT('SELECT date_time, ', @sql, ' 
                     FROM test
                    GROUP BY date_time');

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

Demo

Or its simple if you have limited/determined channel_code then

SELECT
date_time,
MAX(CASE WHEN channel_code = 'no' THEN value  END) `no`,
MAX(CASE WHEN channel_code = 'no2' THEN value  END) `no2`,
MAX(CASE WHEN channel_code = 'nox' THEN value  END) `nox`
FROM test
GROUP BY date_time

Demo

Limiting data in a crosstab query on MySQL

I'm not getting the exact counts you're expecting in but this might help

SELECT  CASE
            WHEN MAKE_MODEL LIKE 'Acura%' THEN 'Acura'
            WHEN MAKE_MODEL LIKE 'Ford%' THEN 'Ford'
            WHEN MAKE_MODEL LIKE 'Toyota%' THEN 'Toyota'
            WHEN MAKE_MODEL LIKE 'Tesla%' THEN 'Tesla'
        END AS Make,
        SUM(CASE WHEN ci.color = 'Black' THEN ci.COUNT ELSE 0 END) as Black,
        SUM(CASE WHEN ci.color = 'Blue' THEN ci.COUNT ELSE 0 END) as Blue,
        SUM(CASE WHEN ci.color = 'White' THEN ci.COUNT ELSE 0 END) as White
FROM    (-- GET DISTINCT COUNTS
         SELECT ci.MAKE_MODEL,
                ci.COLOR,
                ci.YEAR,
                DATE(ci.DATE_ADDED) AS DATE,
                COUNT(DISTINCT MAKE_MODEL) AS COUNT
         FROM   CAR_INVENTORY ci
         WHERE  YEAR > 2012
                AND CAR_ID NOT IN (
                    SELECT DISTINCT il.car_id 
                    FROM inventory_log il 
                    WHERE il.note LIKE '%issue%'
                )
         GROUP BY ci.MAKE_MODEL,
                ci.COLOR,
                ci.YEAR,
                DATE(DATE_ADDED)
        ) ci
GROUP BY CASE
            WHEN MAKE_MODEL LIKE 'Acura%' THEN 'Acura'
            WHEN MAKE_MODEL LIKE 'Ford%' THEN 'Ford'
            WHEN MAKE_MODEL LIKE 'Toyota%' THEN 'Toyota'
            WHEN MAKE_MODEL LIKE 'Tesla%' THEN 'Tesla'
        END

Result:
Make   Black       Blue        White
------ ----------- ----------- -----------
Acura  1           1           0
Ford   3           1           2
Tesla  0           0           1
Toyota 1           2           0

I'm only getting 1 for Acura Black because there are only 2 and 1 is a 2012

SQL Fiddle Demo

Mysql Pivot/crosstab Query using JOIN query

Thanks for help Barmar.

Solution is here.

SELECT i.post_id,
       i.name post_name,
       i.user_id,
       MAX(IF(p.meta_key = "Shipping:",p.meta_value, NULL)) shipping,
       MAX(IF(p.meta_key = "Price:", p.meta_value, NULL)) price,
       MAX(IF(p.meta_key = "Size:", p.meta_value, NULL)) size,
       MAX(IF(p.meta_key = "_wp_attached_file", p.meta_value, NULL)) wp_attached_file
FROM wp_frm_items i
JOIN wp_postmeta p ON i.post_id = p.post_id 
where i.post_id  = 19241 
GROUP BY i.post_id 

Demo

---

Related Topics