我有一个表格,列出了感染不同病毒的不同细胞系中化合物的抗病毒活性,如下所示:
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、病毒和 Conc 的原始行值来生成新列和相应的名称,然后报告每个化合物的效果。对于有限数量的组合,这当然可以是"硬编码",但我希望允许以后添加额外的细胞系、病毒株和浓度的可能性,从而动态生成枢轴列名称。
感谢有关如何在mySQL中有效编码的任何指示!
因此,表的硬编码解决方案非常简单:
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;