MySQL-how to merge multiple rows into multiple columns and one row?

Question

If I have a MySQL table looking something like this: for example 2014000 is a product pen , there are four types abcd with different prices. and 2014001 is another prodct telephone , also there three types efg with different prices. now I want to get the one product one line with names and values.

id       code         name       value

1      2014000          A          10

2      2014000          B          9

3      2014000          C         11

4      2014000          D        12

5      2014001          E        100 

6      2014001          F        110

7     2014001           G        120

respect result：

         code              name1                  value1                 name2              value2               name3              value3             name4              value4  

         -----------       ---------              ---------          ---------              ---------   -----------       ---------              ---------          ---------              
         2014000          A                      10                   B                     9                C                      11                   D                 12
         2014001          E                     100                   F                     110             G                      120                  null              null

CREATE TABLE T (ID INT, code INT, NAME CHAR(1), VALUE VARCHAR(10));

INSERT INTO T VALUES(1,2014000,'A','10'), (2,2014000,'B','9'), (3,2014000,'C','11'), (4,2014000,'D','12'), (5,2014001,'E','100'), (6,2014001,'F','110'), (7,2014001,'G','120');

-----the followed only for two rows , if there are more than four rows, how to do it?

SELECT    T1.code,     T2.name AS name1,     T2.value AS value1,     T3.name AS name2,     T3.value AS value2 FROM(     SELECT code,MIN(ID) AS ID1,CASE COUNT(code) WHEN 1 THEN NULL ELSE MAX(ID) END AS ID2 FROM T GROUP BY code ) T1 LEFT JOIN T T2 ON T1.ID1 = T2.ID LEFT JOIN T T3 ON T1.ID2 = T3.ID

Please only use tags related to your question and format your question properly. — Filburt, Jul 18 '17 at 09:32
Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Filburt, Jul 18 '17 at 09:33

score 1 · Answer 1 · answered Jul 19 '17 at 05:27

SELECT t.code
     , t1.name name1
     , t1.value value1
     , t2.name name2
     , t2.value value2
     , t3.name name3
     , t3.value value3
     , t4.name name4
     , t4.value value4
  FROM ( SELECT code
              , group_concat(ID) ids
           FROM T
           GROUP BY code
       ) t
    LEFT JOIN T t1
      ON find_in_set(t1.ID,t.ids)=1
    LEFT JOIN T t2
      ON find_in_set(t2.ID,t.ids)=2
    LEFT JOIN T t3
      ON find_in_set(t3.ID,t.ids)=3
    LEFT JOIN T t4
      ON find_in_set(t4.ID,t.ids)=4

MySQL-how to merge multiple rows into multiple columns and one row?

1 Answers1