转置MySQL查询-需要将行分为列 [英] Transpose MySQL query - need rows into columns
问题描述
一个菜鸟MYSQL用户....我有一个简单的MySQL查询,该查询返回值,并使用GROUP_CONCAT函数:
a rookie MYSQL user .... I have a simple MySQL query that returns values, and uses the GROUP_CONCAT function:
SELECT Productid, Name, GROUP_CONCAT(value)
FROM search_export
Group By Productid, Name;
Productid Name GROUP_CONCAT(value)
666056542 Brand Name Netgear
1054677552 Ethernet Technology Gigabit Ethernet
665655662 Form Factor Wall Mountable,Desktop
56565765 Media Type Supported Twisted Pair
但是,我需要转置查询,以便它以单独的列而不是行的形式返回名称". MySQL完全可以做到这一点吗?
However, I need to transpose the query so it returns 'Name' as seperate columns, not a row. Is this possible at all with MySQL?
推荐答案
您需要执行PIVOT
操作,MySQL本身不支持该操作(与某些其他RDBMS不同).
You need to perform a PIVOT
operation, which is not supported natively in MySQL (unlike some other RDBMS).
您可以获得的最接近的方法是按照以下几行来构造SQL:
The closest you can get is to construct SQL along the following lines:
SELECT ProductId,
GROUP_CONCAT(IF(Name='Brand Name' ,value,NULL))
AS `Brand Name`,
GROUP_CONCAT(IF(Name='Ethernet Technology' ,value,NULL))
AS `Ethernet Technology`,
GROUP_CONCAT(IF(Name='Form Factor' ,value,NULL))
AS `Form Factor`,
GROUP_CONCAT(IF(Name='Media Type Supported',value,NULL))
AS `Media Type Supported`
FROM search_export
GROUP BY ProductId
如果可能的Name
值是动态的,则可以根据以下结果以更高级的语言生成此类SQL:
If the possible Name
values are dynamic, you could generate such SQL in a higher level language from the results of:
SELECT DISTINCT Name FROM search_export
实际上,甚至可以使用SQL本身:
Indeed, one could even use SQL itself:
SELECT CONCAT('
SELECT ProductId, ',
GROUP_CONCAT('
GROUP_CONCAT(IF(Name=',QUOTE(Name),',value,NULL))
AS `',REPLACE(Name,'`','``'),'`'
), '
FROM search_export
GROUP BY ProductId
')
INTO @sql
FROM (
SELECT DISTINCT Name FROM search_export
) t;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Note that if there are a lot of different Name
values, you may need to increase group_concat_max_len
from its default of 1KiB.
这篇关于转置MySQL查询-需要将行分为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!