您需要执行PIVOT操作,MySQL本身不支持该操作(与其他一些RDBMS不同).
最接近的是沿着以下几行构造SQL:
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:
SELECT DISTINCT Name FROM search_export
实际上,人们甚至可以使用SQL本身:
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;
请注意,如果存在许多不同的Name值,则可能需要从默认值1KiB增加group_concat_max_len.