mysql - 如何将 MySQL 用于动态数据透视表或交叉表

Question

我知道这个问题或其变体已被问过多次，我什至尝试过实现解决方案，但我很难完成它。

我有一个非常简单的表，其中包含三个数据列:Date1、Report_#、Name。我想将它围绕日期和标题选项卡的各种名称进行调整，并将所有报告编号显示在下方。

所以它看起来像这样:

Report | Date   | Name                     Date   | Name1 | Name2 | Name3
-----------------------                    ------------------------------
1      | 4-5-12 | Name1                    4-5-12 | 1     | 2     | 3
2      | 4-5-12 | Name2      ----->        4-6-12 | 4     | 5     | 6
3      | 4-5-12 | Name3                    4-7-12 | 7     | 8     | 9
4      | 4-6-12 | Name1                    
5      | 4-6-12 | Name2                    
6      | 4-6-12 | Name3                    
7      | 4-7-12 | Name1
8      | 4-7-12 | Name2
9      | 4-7-12 | Name3

我能够从 http://www.artfulsoftware.com/infotree/queries.php#78 知道该做什么但我卡住了。

我能够手动隔离列名并在每个名称下列出报告#，但我想动态找出不同的名称，将它们设为列名，并列出相应的报告。

因此，我创建了一个程序来查找不同的名称并输出正确的代码。现在我很难将过程的结果插入到查询中。上面的链接没有任何帮助，(它似乎跳过了那一步)。

下面是手动方式的代码:

SELECT `DATE`, 
GROUP_CONCAT(CASE `Name` WHEN 'Name1' THEN `Report` END) AS 'Name1', 
GROUP_CONCAT(CASE `Name` WHEN 'Name2' THEN `Report` END) AS 'Name2' 
FROM `report_db` GROUP BY `DATE` ORDER BY `DATE`;

下面是动态打印 GROUP_CONCAT(... 用于数据库中所有不同名称的代码:

DROP PROCEDURE IF EXISTS writecountpivot;
DELIMITER | 
CREATE PROCEDURE writecountpivot( db CHAR(64), tbl CHAR(64), col CHAR(64) ) 
BEGIN 
DECLARE datadelim CHAR(1) DEFAULT '"'; 
DECLARE singlequote CHAR(1) DEFAULT CHAR(39); 
DECLARE comma CHAR(1) DEFAULT ','; 
SET @sqlmode = (SELECT @@sql_mode); 
SET @@sql_mode=''; 
  SET @sql = CONCAT( 'SELECT DISTINCT CONCAT(', singlequote, 
                     ',group_concat(IF(', col, ' = ', datadelim, singlequote, comma, 
                     col, comma, singlequote, datadelim, comma, '`IR NO`,null)) AS `',  
                     singlequote, comma, col, comma, singlequote, '`', singlequote,  
                     ') AS countpivotarg FROM ', db, '.', tbl, 
                     ' WHERE ', col, ' IS NOT NULL' ); 
  -- UNCOMMENT TO SEE THE MIDLEVEL CODE: 
  -- SELECT @sql;  
  PREPARE stmt FROM @sql; 
  EXECUTE stmt; 
  DROP PREPARE stmt; 
  SET @@sql_mode=@sqlmode; 
END
| 
DELIMITER ; 
CALL writecountpivot('database','`report_db`','name');

上面代码的结果就是这个

,group_concat(IF(name = "Name1",`IR NO`,null)) AS `Name1`
,group_concat(IF(name = "Name2",`IR NO`,null)) AS `Name2`
,group_concat(IF(name = "Name3",`IR NO`,null)) AS `Name3`

** 那么我如何获取此文本并将其插入到我的 SQL 中呢？如何将过程和查询放在一起？**

Answer 1

很难确切地说出您正在尝试做什么，但是如果您正在尝试创建一个可以执行任意枢轴的过程，那么您将需要为其提供更多参数(例如列要旋转的位置以及可以在其中找到值的列)。

此外，您将需要在准备好的语句中创建准备好的语句。最外面的语句将使用 GROUP_CONCAT() 构造要执行的 GROUP_CONCAT() 表达式，基于指定列中的唯一值:

CREATE FUNCTION SQL_ESC(_identifier VARCHAR(64))
RETURNS VARCHAR(130) DETERMINISTIC
RETURN CONCAT('`',REPLACE(_identifier,'`','``'),'`')//

CREATE PROCEDURE writecountpivot(
  IN _db_nm VARCHAR(64),
  IN _tb_nm VARCHAR(64),
  IN _cl_gp VARCHAR(64),
  IN _cl_pv VARCHAR(64),
  IN _cl_vl VARCHAR(64)
) BEGIN
  SET @sql := CONCAT(
    "SELECT CONCAT('
              SELECT   ",SQL_ESC(_cl_gp),",',
              GROUP_CONCAT(DISTINCT CONCAT(
                'GROUP_CONCAT(IF(",
                   SQL_ESC(_cl_pv),"=',QUOTE(",SQL_ESC(_cl_pv),"),'
                 , ",SQL_ESC(_cl_vl),"
                 , NULL
                 )) AS ',SQL_ESC(",SQL_ESC(_cl_pv),")
              )), '
              FROM     ",SQL_ESC(_db_nm),".",SQL_ESC(_tb_nm),"
              GROUP BY ",SQL_ESC(_cl_gp),"
            ')
     INTO   @sql
     FROM   ",SQL_ESC(_db_nm),".",SQL_ESC(_tb_nm)
  );

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

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

查看 sqlfiddle .