MySQL行到列 [英] MySQL Row to Column

问题描述

任何人都可以帮助我如何使用源表上的行数据作为输出的标题来创建查询输出.请参见下面的插图.

can any one help me on how can I create a query output using the row data on the source table as a header on my output. Please see below for illustration.

E.G.

行数据:

+-----------+----------+
| colHeader | value    |
+-----------+----------+
| Header1   | value 1  |
+-----------+----------+
| Header2   | value 2  |
+-----------+----------+
| Header3   | value 3  |
+-----------+----------+

输出:

+-----------+-----------+-----------+
| Header1   | header2   | Header3   |
+-----------+-----------+-----------+
| Value 1   | value 2   | Value 3   |
+-----------+-----------+-----------+

有可能吗?

这是我的MySQL脚本.我不认为这是正确的方法.我对如何获得上述输出有任何想法吗?

Here is my MySQL script. I don't think if is it the right way. Is there any idea on how could i arrive on the above output?

SELECT t1.value AS `Header1`,
       t2.value AS `Header2`,
       t3.value AS `Header3`
  FROM (SELECT * FROM table1 WHERE colHeader='Header1') t1
  JOIN (SELECT * FROM table1 WHERE colHeader='Header2'3) t2
  JOIN (SELECT * FROM table1 WHERE colHeader='Header3') t3;

推荐答案

如何??

SELECT  
  GROUP_CONCAT(if(colHeader = 'Header 1', value, NULL)) AS 'Header 1', 
  GROUP_CONCAT(if(colHeader = 'Header 2', value, NULL)) AS 'Header 2', 
  GROUP_CONCAT(if(colHeader = 'Header 3', value, NULL)) AS 'Header 3' 
FROM myTable; 

演​​示

请注意，当演示2 中显示的ID数据更多时，您将需要GROUP BY语句.

Demo

Note, you will need GROUP BY statement when there are more data of ids as shown below in Demo 2.

这篇关于MySQL行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！