mysql - MYSQL 表中的多个数据透视表

Question

我的数据库有以下设置:

create table spooner_pec
(
  id int,
  year varchar(20),
  policy_number int,
  primary_name varchar(200),
  experience_rate decimal(10,2),
  mco_name varchar(200)
);

插入这些值:

insert into spooner_pec values
(1,'2009',183586,'ZBIN LANDSCAPING INC', 1.22, 'GENEX CARE FOR OHIO'),
(1,'2011',183586,'ZBIN LANDSCAPING INC', 0.93, 'COMPMANAGEMENT HEALTH SYSTEMS, INC.'),
(1,'2012',183586,'ZBIN LANDSCAPING INC', 0.92, 'HEALTH MANAGEMENT SOLUTIONS, INC.'),
(1,'2013',183586,'ZBIN LANDSCAPING INC', 0.50, 'CAREWORKS'),
(1,'2014',183586,'ZBIN LANDSCAPING INC', 0.47, 'UNIVERSITY HOSPITALS COMPCARE'),
(1,'2010',183586,'ZBIN LANDSCAPING INC', 1.27, 'SHEAKLEY UNICOMP')

我目前使用的查询是这样的:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when year = ',
      year,
      ' then experience_rate end) AS `',
      year, '-Pen`'
    ) ORDER BY year
  ) INTO @sql
FROM
  spooner_pec;
SET @sql = CONCAT('SELECT policy_number, primary_name, ', @sql, ' 
                  FROM spooner_pec 
                   GROUP BY policy_number');

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

因此，经过这里的先前问题和一些研究后，我终于让这个枢轴正常工作。我基本上只是把所有的年份都变成列而不是行。

所以我每家公司都有一行，然后每一年都有一个年笔列。现在我还需要以同样的方式有一个 year-mco 列，我应该在我的声明中的什么地方添加它？

SQLFiddle

Answer 1

这是您要找的吗？

SET @sql = NULL;
SET @sql2 = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when year = ',
      year,
      ' then experience_rate end) AS `',
      year, '-Pen`'
    ) ORDER BY year
  ) INTO @sql
FROM
  spooner_pec;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when year = ',
      year,
      ' then mco_name end) AS `',
      year, '-Mco`'
    ) ORDER BY year
  ) INTO @sql2
FROM
  spooner_pec;
SET @sql = CONCAT('SELECT policy_number, primary_name, ', @sql, ', ', @sql2, ' 
              FROM spooner_pec 
               GROUP BY policy_number');

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