Transpose MySQL query - need rows into columns

Question

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

However, I need to transpose the query so it returns 'Name' as seperate columns, not a row. Is this possible at all with MySQL?

Hi,Yes, multiple records with different names - I need each 'Name' row as a column, so for e.g. Brand Name | Ethernet Technology | Form Factor | Media Type Supported — Marc
– Marc, Commented Nov 27, 2012 at 10:04

eggyal · Accepted Answer · 2014-03-28 20:32:03Z

You need to perform a PIVOT operation, which is not supported natively in MySQL (unlike some other RDBMS).

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

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

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.

tip on group_concat_max_len is very helpful .. .. very good explanation

Collectives™ on Stack Overflow

Transpose MySQL query - need rows into columns

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related