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
– Filburt, Commented Jul 18, 2017 at 9:32

Sal · Accepted Answer · 2017-07-19 05:27:34Z

1

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

answered Jul 19, 2017 at 5:27

Sal

1,3171 gold badge8 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related