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

12

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.

edited Mar 28, 2014 at 20:32

answered Nov 27, 2012 at 10:15

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

UberNeo Over a year ago

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related