重塑表格以将行转换为列

Question

我尝试搜索帖子，但只找到了 SQL Server/Access 的解决方案。 我需要 MySQL (5.X) 中的解决方案。

我有一个包含 3 列的表（称为历史记录）：hostid、itemname、itemvalue。
如果我执行选择（select * from History），它将返回

hostid	itemname	itemvalue
1	A	10
1	B	3
2	A	9
2	C	40

如何查询数据库以返回类似

hostid	A	B 的	内容C
1	10	3	0
2	9	0	40

Answer 1

您可以使用几个LEFT JOIN。 请使用此代码

SELECT t.hostid,
       COALESCE(t1.itemvalue, 0) A,
       COALESCE(t2.itemvalue, 0) B,
       COALESCE(t3.itemvalue, 0) C 
FROM history t 
LEFT JOIN history t1 
    ON t1.hostid = t.hostid 
    AND t1.itemname = 'A' 
LEFT JOIN history t2 
    ON t2.hostid = t.hostid 
    AND t2.itemname = 'B' 
LEFT JOIN history t3 
    ON t3.hostid = t.hostid 
    AND t3.itemname = 'C' 
GROUP BY t.hostid

Answer 2

SELECT 
    hostid, 
    sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,  
    sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, 
    sum( if( itemname = 'C', itemvalue, 0 ) ) AS C 
FROM 
    bob 
GROUP BY 
    hostid;

Answer 3

如果您有许多需要旋转的项目，另一个选项特别有用，那就是让 mysql 为您构建查询：

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when itemname = ''',
      itemname,
      ''' then itemvalue end),0) AS `',
      itemname, '`'
    )
  ) INTO @sql
FROM
  history;
SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                  FROM history 
                   GROUP BY hostid');

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

FIDDLE
添加了一些额外的值以查看其工作情况

GROUP_CONCAT 的默认值为 1000，因此如果您有一个非常大的查询，请在运行

SET SESSION group_concat_max_len = 1000000;

测试之前更改此参数：

DROP TABLE IF EXISTS history;
CREATE TABLE history
(hostid INT,
itemname VARCHAR(5),
itemvalue INT);

INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),
(2,'C',40),(2,'D',5),
(3,'A',14),(3,'B',67),(3,'D',8);

  hostid    A     B     C      D
    1     10      3     0      0
    2     9       0    40      5
    3     14     67     0      8

Answer 4

我将添加更长、更详细的说明来说明解决此问题所需的步骤。 如果太长，我深表歉意。

---

我将从您给出的基础开始，并用它来定义几个术语，我将在本文的其余部分使用这些术语。 这将是基表：

select * from history;

+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
|      1 | A        |        10 |
|      1 | B        |         3 |
|      2 | A        |         9 |
|      2 | C        |        40 |
+--------+----------+-----------+

这将是我们的目标，漂亮的数据透视表：

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

history.hostid 列中的值将变为数据透视表中的 >y 值。 history.itemname 列中的值将变为 x-values（出于显而易见的原因）。

---

当我必须解决创建数据透视表的问题时，我使用三步过程（带有可选的第四步）来解决它：

1. 选择感兴趣的列，即 y 值 和 >x 值
2. 使用额外的列扩展基表 - 每个 x 值
3. 组一列，并聚合扩展表 - 每个 y 值一组） strong>
4. （可选）美化聚合表

让我们将这些步骤应用于您的问题，看看会得到什么：

第 1 步：选择感兴趣的列。 在所需的结果中，hostid 提供y 值，itemname 提供x 值。

第 2 步：使用额外的列扩展基表。 我们通常需要每个 x 值一列。 回想一下，我们的 x 值列是 itemname：

create view history_extended as (
  select
    history.*,
    case when itemname = "A" then itemvalue end as A,
    case when itemname = "B" then itemvalue end as B,
    case when itemname = "C" then itemvalue end as C
  from history
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

请注意，我们没有更改行数 - 我们只是添加了额外的列。 另请注意 NULL 的模式 - 具有 itemname = "A" 的行的新列 A 具有非空值，并且其他新列的空值。

第3步：对扩展表进行分组和聚合。 我们需要按主机 ID 进行分组，因为它提供了 y 值：

create view history_itemvalue_pivot as (
  select
    hostid,
    sum(A) as A,
    sum(B) as B,
    sum(C) as C
  from history_extended
  group by hostid
);

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

（请注意，我们现在每个 y 值一行。） 好的，我们就快到了！ 我们只需要摆脱那些丑陋的NULL。

第四步：美化。 我们将用零替换任何空值，以便结果集看起来更好：

create view history_itemvalue_pivot_pretty as (
  select 
    hostid, 
    coalesce(A, 0) as A, 
    coalesce(B, 0) as B, 
    coalesce(C, 0) as C 
  from history_itemvalue_pivot 
);

select * from history_itemvalue_pivot_pretty;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

我们就完成了——我们已经使用 MySQL 构建了一个漂亮的数据透视表。

---

应用此过程时的注意事项：

• 在额外列中使用什么值。 在此示例中，我使用了 itemvalue
• 在额外列中使用的“中性”值。 我使用了 NULL，但也可以是 0 或 ""，具体取决于您的具体情况，
• 分组时使用什么聚合函数。 我使用了 sum，但是 count 和 max 也经常使用（max 经常在构建单行时使用） 使用多列
• 作为 y 值。 此解决方案不限于对 y 值使用单个列 - 只需将额外的列插入 group by 子句（并且不要忘记 select他们）

已知的限制：

• 此解决方案不允许数据透视表中有 n 列——扩展基表时需要手动添加每个数据透视列。 因此，对于 5 或​​ 10 个 x 值，此解决方案很好。 100块，不太好。 有一些使用存储过程生成查询的解决方案，但它们很丑陋并且很难正确执行。 当数据透视表需要有很多列时，我目前不知道有什么好方法来解决这个问题。

Answer 5

利用 Matt Fenwick 帮助我解决问题的想法（非常感谢），让我们将其减少到只有一个查询：

select
    history.*,
    coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
    coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
    coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
from history
group by hostid

Answer 6

我从子查询中编辑 Agung Sagita 的答案来加入。
我不确定这两种方式有多大区别，仅供参考。

SELECT  hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
FROM TableTest AS T1
LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME='A'
LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME='B'
LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME='C'

Answer 7

使用子查询，

SELECT  hostid, 
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='A' AND hostid = t1.hostid) AS A,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='B' AND hostid = t1.hostid) AS B,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='C' AND hostid = t1.hostid) AS C
FROM TableTest AS T1
GROUP BY hostid

但如果子查询结果超过一行，就会出现问题，在子查询中使用进一步的聚合函数

Answer 8

我的解决方案：

select h.hostid, sum(ifnull(h.A,0)) as A, sum(ifnull(h.B,0)) as B, sum(ifnull(h.C,0)) as  C from (
select
hostid,
case when itemName = 'A' then itemvalue end as A,
case when itemName = 'B' then itemvalue end as B,
case when itemName = 'C' then itemvalue end as C
  from history 
) h group by hostid

它在提交的案例中产生预期结果。

Answer 9

如果您可以使用 MariaDB 有一个非常非常简单的解决方案。

自 MariaDB-10.02 起，添加了一个名为 CONNECT< 的新存储引擎/a> 可以帮助我们将另一个查询或表的结果转换为数据透视表，就像您想要的那样：
您可以查看文档。

首先安装连接存储引擎。

现在我们表的数据透视表列是 itemname 并且每个项目的数据位于 itemvalue 列中，因此我们可以使用此查询获得结果数据透视表：

create table pivot_table
engine=connect table_type=pivot tabname=history
option_list='PivotCol=itemname,FncCol=itemvalue';

现在我们可以从pivot_table中选择我们想要的内容：

select * from pivot_table

更多详细信息在这里

Answer 10

我将其放入 Group By hostId 中，然后它将仅显示带有值的第一行，
喜欢：

A   B  C
1  10
2      3

Answer 11

我找到了一种方法，可以使用简单的查询使我的报告将行转换为几乎动态的列。 您可以在此处在线查看并测试它。

查询的列数是固定的，但值是动态的并且基于行的值。 您可以构建它因此，我使用一个查询来构建表头，并使用另一个查询来查看值：

SELECT distinct concat('<th>',itemname,'</th>') as column_name_table_header FROM history order by 1;

SELECT
     hostid
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue else '' end) as col1
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue else '' end) as col2
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue else '' end) as col3
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 3,1) then itemvalue else '' end) as col4
FROM history order by 1;

您也可以对其进行总结：

SELECT
     hostid
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue end) as A
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue end) as B
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue end) as C
FROM history group by hostid order by 1;
+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

RexTester:

http://rextester. com/ZSWKS28923

作为一个真实的使用示例，下面的报告以列形式显示了船只/公共汽车的出发到达时间和可视时间表。 您将看到最后一列未使用的附加列，而不会混淆可视化：

** 票务系统在线售票和赠品

Answer 12

这不是您正在寻找的确切答案，但这是我的项目所需的解决方案，希望这对某人有帮助。 这将列出 1 到 n 行项目，以逗号分隔。 Group_Concat 在 MySQL 中使这成为可能。

select
cemetery.cemetery_id as "Cemetery_ID",
GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
cemetery.latitude as Latitude,
cemetery.longitude as Longitude,
c.Contact_Info,
d.Direction_Type,
d.Directions

    from cemetery
    left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id 
    left join names on cemetery_names.name_id = names.name_id 
    left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id 

    left join 
    (
        select 
            cemetery_contact.cemetery_id as cID,
            group_concat(contacts.name, char(32), phone.number) as Contact_Info

                from cemetery_contact
                left join contacts on cemetery_contact.contact_id = contacts.contact_id 
                left join phone on cemetery_contact.contact_id = phone.contact_id 

            group by cID
    )
    as c on c.cID = cemetery.cemetery_id


    left join
    (
        select 
            cemetery_id as dID, 
            group_concat(direction_type.direction_type) as Direction_Type,
            group_concat(directions.value , char(13), char(9)) as Directions

                from directions
                left join direction_type on directions.type = direction_type.direction_type_id

            group by dID


    )
    as d on d.dID  = cemetery.cemetery_id

group by Cemetery_ID

该墓地有两个通用名称，因此这些名称列在由单个 id 连接的不同行中，但有两个名称 id，查询会生成类似以下内容的内容

   CemeteryID    Cemetery_Name            纬度
   1               阿普尔顿，萨尔弗斯普林斯   35.4276242832293

Answer 13

我很抱歉这么说，也许我没有完全解决你的问题，但 PostgreSQL 比 MySQL 早 10 年，并且比 MySQL 更加先进，并且有很多方法可以轻松实现这一点。 安装 PostgreSQL 并执行此查询

CREATE EXTENSION tablefunc;

，然后瞧！ 这里有大量文档： PostgreSQL: Documentation: 9.1: tablefunc 或此

CREATE EXTENSION hstore;

查询再次瞧！ PostgreSQL：文档：9.0：hstore