MySQL - 行到列

Question

提问by Bob Rivers

I tried to search posts, but I only found solutions for SQL Server/Access. I need a solution in MySQL (5.X).

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

I have a table (called history) with 3 columns: hostid, itemname, itemvalue.
If I do a select (select * from history), it will return

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

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

How do I query the database to return something like

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

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

Answer 1

回答by Matt Fenwick

I'm going to add a somewhat longer and more detailed explanation of the steps to take to solve this problem. I apologize if it's too long.

我将添加更长更详细的解释，说明解决此问题的步骤。如果太长，我很抱歉。

I'll start out with the base you've given and use it to define a couple of terms that I'll use for the rest of this post. This will be the base table:

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

select * from history;

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

This will be our goal, the pretty pivot table:

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

select * from history_itemvalue_pivot;

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

Values in the history.hostidcolumn will become y-valuesin the pivot table. Values in the history.itemnamecolumn will become x-values(for obvious reasons).

在值history.hostid列将成为Y值数据透视表。在值history.itemname列将成为x值（原因很明显）。

When I have to solve the problem of creating a pivot table, I tackle it using a three-step process (with an optional fourth step):

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

select the columns of interest, i.e. y-valuesand x-values
extend the base table with extra columns -- one for each x-value
group and aggregate the extended table -- one group for each y-value
(optional) prettify the aggregated table

选择感兴趣的列，即y 值和x 值
用额外的列扩展基表——每个x 值一个
对扩展表进行分组和聚合——每个y 值一组
（可选）美化聚合表

Let's apply these steps to your problem and see what we get:

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

Step 1: select columns of interest. In the desired result, hostidprovides the y-valuesand itemnameprovides the x-values.

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

Step 2: extend the base table with extra columns. We typically need one column per x-value. Recall that our x-value column is itemname:

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

Note that we didn't change the number of rows -- we just added extra columns. Also note the pattern of NULLs -- a row with itemname = "A"has a non-null value for new column A, and null values for the other new columns.

请注意，我们没有更改行数——我们只是添加了额外的列。还要注意NULLs的模式- 一行itemname = "A"的 new column 具有非A空值，其他新列具有空值。

Step 3: group and aggregate the extended table. We need to group by hostid, since it provides the y-values:

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

(Note that we now have one row per y-value.)Okay, we're almost there! We just need to get rid of those ugly NULLs.

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

Step 4: prettify. We're just going to replace any null values with zeroes so the result set is nicer to look at:

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

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

And we're done -- we've built a nice, pretty pivot table using MySQL.

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

Considerations when applying this procedure:

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

what value to use in the extra columns. I used itemvaluein this example
what "neutral" value to use in the extra columns. I used NULL, but it could also be 0or "", depending on your exact situation
what aggregate function to use when grouping. I used sum, but countand maxare also often used (maxis often used when building one-row "objects" that had been spread across many rows)
using multiple columns for y-values. This solution isn't limited to using a single column for the y-values -- just plug the extra columns into the group byclause (and don't forget to selectthem)

在额外的列中使用什么值。我itemvalue在这个例子中使用
在额外的列中使用什么“中性”值。我用过NULL，但也可以是0或""，具体取决于您的具体情况
分组时使用什么聚合函数。我用sum，但count并max也经常使用（max构建跨越了很多行已经扩展一排“对象”时经常使用）
对 y 值使用多列。此解决方案不限于对 y 值使用单列——只需将额外的列插入group by子句中（不要忘记select它们）

Known limitations:

已知限制：

this solution doesn't allow n columns in the pivot table -- each pivot column needs to be manually added when extending the base table. So for 5 or 10 x-values, this solution is nice. For 100, not so nice. There are some solutions with stored procedures generating a query, but they're ugly and difficult to get right. I currently don't know of a good way to solve this problem when the pivot table needs to have lots of columns.

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

Answer 2

回答by shantanuo

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

回答by Mihai

Another option,especially useful if you have many items you need to pivot is to let mysql build the query for you:

另一个选项，如果您有许多需要转置的项目，则特别有用，那就是让 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;

FIDDLEAdded some extra values to see it working

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

GROUP_CONCAThas a default value of 1000 so if you have a really big query change this parameter before running it

GROUP_CONCAT有一个默认值 1000 所以如果你有一个非常大的查询在运行它之前改变这个参数

SET SESSION group_concat_max_len = 1000000;

Test:

测试：

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

回答by jalber

Taking advantage of Matt Fenwick's idea that helped me to solve the problem (a lot of thanks), let's reduce it to only one query:

利用 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 5

回答by haudoing

I edit Agung Sagita's answer from subquery to join. I'm not sure about how much difference between this 2 way, but just for another reference.

我从子查询中编辑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 6

回答by Agung Sagita

use subquery

使用子查询

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

but it will be a problem if sub query resulting more than a row, use further aggregate function in the subquery

但是如果子查询产生多于一行，这将是一个问题，在子查询中使用进一步的聚合函数

Answer 7

回答by André Wéber

My solution :

我的解决方案：

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

It produces the expected results in the submitted case.

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

Answer 8

回答by arpit

I make that into Group By hostIdthen it will show only first row with values,
like:

我把它变成Group By hostId然后它只会显示第一行的值，
比如：

A   B  C
1  10
2      3

Answer 9

回答by lynx_74

I figure out one way to make my reports converting rows to columns almost dynamic using simple querys. You can see and test it online here.

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

The number of columns of query is fixedbut the values are dynamicand based on values of rows. You can build it So, I use one query to build the table header and another one to see the values:

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

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;

You can summarize it, too:

你也可以总结一下：

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

Results of RexTester:

RexTester 的结果：

http://rextester.com/ZSWKS28923

For one real example of use, this report bellow show in columns the hours of departures arrivals of boat/bus with a visual schedule. You will see one additional column not used at the last col without confuse the visualization: ** ticketing system to of sell ticket online and presential

对于一个真实的使用示例，此报告在列中以可视化时间表显示了船只/公共汽车的出发到达时间。您将看到在最后一列未使用的附加列，而不会混淆可视化： ** 在线售票和演示售票系统

Answer 10

回答by ako

If you could use MariaDBthere is a very very easy solution.

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

Since MariaDB-10.02there has been added a new storage engine called CONNECTthat can help us to convert the results of another query or table into a pivot table, just like what you want: You can have a look at the docs.

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

First of all install the connect storage engine.

首先安装connect存储引擎。

Now the pivot column of our table is itemnameand the data for each item is located in itemvaluecolumn, so we can have the result pivot table using this query:

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

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

Now we can select what we want from the pivot_table:

现在我们可以从以下选项中选择我们想要的pivot_table：

select * from pivot_table

More details here

更多细节在这里

MySQL - 行到列

提问by Bob Rivers

回答by Matt Fenwick

回答by shantanuo

回答by Mihai

回答by jalber

回答by haudoing

回答by Agung Sagita

回答by André Wéber

回答by arpit

回答by lynx_74

回答by ako

相关推荐

最近更新

标签

MySQL - 行到列

提问by Bob Rivers

回答by Matt Fenwick

回答by shantanuo

回答by Mihai

回答by jalber

回答by haudoing

回答by Agung Sagita

回答by André Wéber

回答by arpit

回答by lynx_74

回答by ako

相关推荐

MySQL 错误：#1142 - 拒绝用户的 SELECT 命令

MySQL DAYOFWEEK() - 我的一周从星期一开始

MySQL 哪种 DATATYPE 更适合使用 TEXT 或 VARCHAR？

尝试通过 JDBC 将 UTF-8 插入 MySQL 时出现“不正确的字符串值”？

相关推荐

最近更新

标签