mysql - Mariadb 使用 True/False 将动态数据从行移动到列

Question

我使用的是 MariaDB 10.2。

客户标签表如下所示

我想创建一个 View /查询，其输出如下

我可以在 Excel 中轻松完成此操作，但 SQL 查询被证明非常困难且错误。

我尝试过 SQL

SELECT
  GROUP_CONCAT( t.tagname ) INTO @PivotQuery
FROM
  (SELECT
     clienttag.customer, tagname
   FROM
     clienttag
   GROUP BY
     clienttag.customer, clienttag.office) t;

SET @PivotQuery = CONCAT('SELECT @PivotQuery' FROM clienttag GROUP BY customer');

Answer 1

使用条件聚合

SELECT customer, tagname,
      MAX(CASE WHEN tagName = 'A' THEN 1 END) as A,
      MAX(CASE WHEN tagName = 'B' THEN 1 END) as B,
      MAX(CASE WHEN tagName = 'C' THEN 1 END) as C,
      MAX(CASE WHEN tagName = 'D' THEN 1 END) as D,
      MAX(CASE WHEN tagName = 'E' THEN 1 END) as E
FROM clienttag
GROUP BY customer, tagname