MySQL-行到列_编程黑洞网

我尝试搜索帖子，但仅找到SQL Server / Access的解决方案。我需要MySQL（5.X）解决方案。

我有一个表（称为历史记录），其中包含3列：hostid，itemname，itemvalue。
如果我执行select（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 |
   +--------+------+-----+-----+

@Rob，能否请您编辑问题以包括确切的查询？

参见stackoverflow.com/a/56670844/3900932

注意：@ako的链接仅与MariaDB有关。

自动生成和运行枢轴：mysql.rjweb.org/doc.php/pivot

#1 楼

我将对解决该问题所要采取的步骤添加一个稍长且更详细的说明。如果太长，我深表歉意。

我将从您给出的基础入手，并用它来定义几个术语，以供后续使用发布。这将是基本表：

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值（出于显而易见的原因）。

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

选择感兴趣的列，即y值和x值

用额外的列扩展基表- -每个x值一个

分组并聚合扩展表-每个y值一个分组

（可选）美化聚合表

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

步骤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具有非空值，其他新列具有null值。

步骤3：对扩展表进行分组和汇总。我们需要group by hostid，因为它提供了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 s。

步骤4：美化。我们将用零替换任何空值，以便更好地查看结果集：

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 |
+--------+------+------+------+

我们已经完成了-我们已经建立了

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

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

已知的局限性：

此解决方案不允许数据透视表中有n列-扩展基表时，需要手动添加每个数据透视列。因此对于5或10个x值，此解决方案很好。对于100，不是很好。存储过程可以生成一些解决方案来生成查询，但是它们很丑陋，很难解决。当数据透视表需要很多列时，我目前不知道解决此问题的好方法。

+1这是迄今为止我所见过的关于MySQL数据透视表/交叉表的最佳/最清晰的解释

– cameron.bracken
2012年8月21日在16:24

很好的解释，谢谢。通过使用IFNULL（sum（A），0）AS A，可以将步骤4合并到步骤3中，从而获得相同的结果，但无需创建另一个表

–neanea82
13年3月20日在10:33

对于枢轴而言，这是最了不起的解决方案，但是我很好奇，如果在形成x轴的列itemname中具有多个值，例如这里我们只有三个值，即A，B，C。如果这些值扩展到A， B，C，D，E，AB，BC，AC，AD，H ..... n。那么在这种情况下，解决方案是什么。

–深层
16-2-24在11:45

这应该是这里公认的答案。它更加详细，有用，并解释了如何理解它，而不仅仅是链接到某些文章（例如当前接受的文章）

– EdgeCaseBerg
16年6月6日在16:43

@WhiteBig请查看日期-这个StackOverflow答案是在那篇博客文章的1.5年之前撰写的。也许您应该改为要求博客向我表示赞赏。

–马特·芬威克（Matt Fenwick）
17-12-7在11:23

#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;

为“ A”，“ B”，“ C”创建三个不同的行

–帕拉尼
2012年3月9日，11：48

@Palani：不，不是。查看分组依据。

–ruakh
2012年3月17日在1:31

谢谢，这对我有用！但是，仅几年之后，我就不得不使用MAX而不是SUM，因为我的itemValue是字符串，而不是数值。

– Merricat
6月17日17:55

如果itemname是动态的怎么办？

– Prat
12月10日6:37

#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

@Mihai也许你可以帮助我。看这个：stackoverflow.com/questions/51832979/…

–成功男人
18年8月14日在3:22

可以将'ifnull（SUM（当itemname ='''时以'''然后itemvalue结尾），0）AS'，`简化为'SUM（当itemname ='''并以'''然后itemvalue else 0结尾）AS'，。这将输出类似SUM的术语（如果itemname ='A'，则itemvalue否则为0结束）为AS'A'。

–ToolmakerSteve
7月8日在1:14

#4 楼

利用马特·芬威克（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

#5 楼

我将子查询中的Agung Sagita的答案编辑为join。
我不确定这两种方式之间有多少区别，但仅供参考。

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'

可能是一个更快的解决方案。

–jave.web
16年4月7日在13:30

我不这么认为。因为左联接有它自己的延迟！

–阿巴迪斯
19年1月3日，14：35

#6 楼

使用子查询

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

，但是如果子查询的结果超过一行，则将出现问题，请在子查询中使用更多的聚合函数

#7 楼

我的解决方案：

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

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

#8 楼

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

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

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

对于一个真实的使用示例，此报告在下面的栏中以可视化的时间表显示了船/公共汽车的出发时间。您将看到上一栏未使用的另一列，而不会混淆可视化效果：

**票务系统用于在线销售和展示门票

#9 楼

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

A   B  C
1  10
2      3

#10 楼

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

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

首先安装连接存储引擎。

现在安装数据透视表我们的表的列是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
  
 
更多详细信息

#11 楼

这不是您要查找的确切答案，但这是我在项目中需要的解决方案，希望对您有所帮助。这将列出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 Appleton，Sulpher Springs 35.4276242832293

#12 楼

抱歉，我可能无法完全解决您的问题，但是PostgreSQL比MySQL早10年，并且与MySQL相比非常先进，并且有许多方法可以轻松实现这一点。安装PostgreSQL并执行此查询

CREATE EXTENSION tablefunc;

然后瞧瞧！这是广泛的文档：PostgreSQL：文档：9.1：tablefunc或此查询

CREATE EXTENSION hstore;

然后再次瞧瞧！ PostgreSQL：文档：9.0：hstore

编程黑洞网

MySQL-行到列

评论