如何使用GROUP_CONCAT动态透视和生成连接列名

Question

我有一个表格，列出了不同细胞系中被不同病毒感染的化合物的抗病毒活性，如下所示：

ID  Batch_ID    Cell_Line   Virus   Conc    Effect
1   abc123      U87         ZIKV    5       67
2   abc123      SW13        HAZV    10      35
3   def456      U87         ZIKV    5       85
4   def456      SW13        HAZV    10      15

我想将其转换为如下所示的视图：

ID  Batch_ID    U87_ZIKV_5  SW13_HAZV_10
1   abc123      67          35
2   def456      85          15

因此，基本上是一个透视，它通过动态连接Cell_Line、Virus和Conc的原始行值来生成新的列和相应的名称，然后报告每个复合的效果。对于有限数量的组合，这当然可以是“硬编码”的，但我希望考虑到以后添加额外的细胞系、病毒株和浓度的可能性，因此可以动态生成旋转列名。

非常感谢任何关于如何在mySQL中高效地编写代码的建议！

Answer 1

因此，您的表的硬编码解决方案非常简单：

select Batch_ID,
  SUM(CASE WHEN CONCAT(Cell_Line,'_',Virus,'_',CONVERT(Conc,CHAR))= 'U87_ZIKV_5' THEN Effect END) U87_ZIKV_5,
  SUM(CASE WHEN CONCAT(Cell_Line,'_',Virus,'_',CONVERT(Conc,CHAR)) = 'SW13_HAZV_10' THEN Effect END) SW13_HAZV_10
from viruses
group by Batch_ID;

但是如果你想让它变得灵活，你应该使用动态数据透视表。我使用了讨论here的方法，并提出了以下MySQL代码，它应该可以解决您的问题：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN CONCAT(Cell_Line,\'_\',Virus,\'_\',CONVERT(Conc,CHAR)) =  ''',
      CONCAT(Cell_Line,'_',Virus,'_',CONVERT(Conc,CHAR)),
      ''' THEN Effect END) AS ',
      CONCAT(Cell_Line,'_',Virus,'_',CONVERT(Conc,CHAR))
    )
  ) INTO @sql
from viruses;

SET @sql = CONCAT('SELECT Batch_ID, ', @sql, ' from viruses
group by Batch_ID');

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