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

4ioopgfo 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(291)

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

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

所以基本上是一个pivot，它通过动态连接cell\u line、virus和conc的原始行值来生成新的列和相应的名称，然后报告每个化合物的效果。对于有限数量的组合，这当然可以是“硬编码”的，但我想考虑到以后添加额外细胞系、病毒株和浓度的可能性，因此可以动态生成数据透视列名。
感谢所有关于如何在mysql中高效地编写代码的指导！

mysql pivot group-concat

来源：https://stackoverflow.com/questions/51837147/how-to-use-group-concat-to-pivot-and-generate-concatenated-column-names-dynamica

1条答案

按热度按时间

ckocjqey1#

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

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;

但是如果你想让它变得灵活，你应该使用动态透视表。我使用了这里讨论的方法，并提出了这个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;

赞(0）回复(0）举报 2021-06-23

我来回答

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

1条答案

相关问题

热门标签

最新问答