我的解决方案是在T-SQL没有任何枢轴:

SELECT
    CompanyName,  
    SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
    SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM 
    Company
GROUP BY 
    CompanyName

SELECT company_name, SUM(CASE WHEN ACTION = 'Email' THEN 1 ELSE 0 END) AS "Email",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 1 THEN 1 ELSE 0 END) AS "print 1 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 2 THEN 1 ELSE 0 END) AS "print 2 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 3 THEN 1 ELSE 0 END) AS "print 2 PAGE"
FROM test1 GROUP BY company_name;

一种选择是结合使用CASE..WHEN语句在MySQL数据库的聚合中是冗余的，并动态考虑所需的查询生成，同时为结果集获得适当的列标题，如下代码块所示:

SET @sql = NULL;

SELECT GROUP_CONCAT(
             CONCAT('SUM( `action` = ''', action, '''',pc0,' ) AS ',action,pc1)
       )
  INTO @sql
  FROM 
  ( 
   SELECT DISTINCT `action`, 
          IF(`pagecount` IS NULL,'',CONCAT('page',`pagecount`)) AS pc1,
          IF(`pagecount` IS NULL,'',CONCAT(' AND `pagecount` = ', pagecount, '')) AS pc0
     FROM `tab` 
    ORDER BY CONCAT(action,pc0) 
  ) t;

SET @sql = CONCAT('SELECT company_name,',@sql,' FROM `tab` GROUP BY company_name'); 
SELECT @sql; 

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

Demo

正确答案是:

select table_record_id,
group_concat(if(value_name='note', value_text, NULL)) as note
,group_concat(if(value_name='hire_date', value_text, NULL)) as hire_date
,group_concat(if(value_name='termination_date', value_text, NULL)) as termination_date
,group_concat(if(value_name='department', value_text, NULL)) as department
,group_concat(if(value_name='reporting_to', value_text, NULL)) as reporting_to
,group_concat(if(value_name='shift_start_time', value_text, NULL)) as shift_start_time
,group_concat(if(value_name='shift_end_time', value_text, NULL)) as shift_end_time
from other_value
where table_name = 'employee'
and is_active = 'y'
and is_deleted = 'n'
GROUP BY table_record_id

有一个叫做MySQL数据透视表生成器的工具，它可以帮助你创建一个基于web的数据透视表，然后你可以导出到excel(如果你喜欢的话)。如果数据在单个表或多个表中，它都可以工作。

您需要做的就是指定列(它支持动态列)、行、表主体中的值和表关系(如果有的话)的数据源。

该工具的主页为https://mysqlreports.com/mysql-reporting-tools/mysql-pivot-table/

使用布尔逻辑的标准sql版本:

SELECT company_name
     , COUNT(action = 'EMAIL' OR NULL) AS "Email"
     , COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
     , COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
     , COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM   tbl
GROUP  BY company_name;

db < >小提琴 老sqlfiddle

How?

TRUE或NULL产生TRUE。 FALSE或NULL产生NULL。 NULL或NULL产生NULL。 COUNT只计算非空值。瞧。