title: MySQL-how to merge multiple rows into multiple columns and one row?
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>respect result：</p>
  
  <pre><code>         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    
  </code></pre>
  
  <p>CREATE TABLE T (ID INT, code INT, NAME CHAR(1), VALUE VARCHAR(10));</p>
  
  <p>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'); </p>
  
  <p>-----the followed only for two rows , if there are more than four rows, how to do it?</p>
  
  <pre><code>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
  </code></pre>