MySQL Transpose one column based on group

Question

I have a table that looks like this:

Column1 Column2
1       A
1       B
1       C
2       D
2       A
2       B
2       E
2       F
3       A
3       D
3       C
3       H
3       J

Is there a query in MySQL to display the information as below:

Column1   Header1    Header2    Header3   Header4   Header5
1         A          B          C       
2         D          A          B         E         F
3         A          D          C         H         J

Answer 1

this is possible... you will have to simulate a pivot table by faking it with MAX() and then a condition inside. you just have to know how many columns you want..

NOTE:

i just called the table letters since thats what it looks like from my position.

QUERY:

SELECT 
    id, 
    MAX(CASE counter WHEN 1 THEN letter ELSE ' ' END), 
    MAX(CASE counter WHEN 2 THEN letter ELSE ' ' END) ,
    MAX(CASE counter WHEN 3 THEN letter ELSE ' ' END) ,
    MAX(CASE counter WHEN 4 THEN letter ELSE ' ' END) ,
    MAX(CASE counter WHEN 5 THEN letter ELSE ' ' END) 
FROM
(   SELECT 
        id, letter, 
        IF(@A = id, @B := @B + 1, @B := 1) AS counter, 
        @A := id
    FROM letters
    CROSS JOIN(SELECT @A := 0, @B := 0) t
) temp
GROUP BY id;

so for this query i put a counter for the id.. counts the number of occurrences for the id... so then you just do MAX(CASE counter WHEN 1-5 THEN col2 ELSE ' ' END meaning when the counter is either 1 - 5 (could be more if you have more ids) put in the letter that corresponds to it or else put in a blank.. hope thats helpful!

RESULTS:

+----+------+-------+-------+-------+-------+
| ID | COL1 | COL2  | COL3  | COL4  | COL5  |
+----+------+-------+-------+-------+-------+
| 1  |  A   |   B   |   C   |               |
| 2  |  D   |   A   |   B   |   E   |   F   |
| 3  |  A   |   D   |   C   |   H   |   J   |
+----+------+-------+-------+-------+-------+

DEMO

Answer 2

The simple answer is yes there is..

The basic query you are looking for is:

  SELECT column1, GROUP_CONCAT(column2 SEPARATOR ' ')
    FROM table_name
GROUP BY column1

You can even order the results:

  SELECT column1, GROUP_CONCAT(column2 ORDER BY column2 SEPARATOR ' ')
    FROM table_name
GROUP BY column1
ORDER BY column1

Answer 3

Or, very similar to (and slightly slower than) JR's solution...

  DROP TABLE IF EXISTS my_table;

  CREATE TABLE my_table
  (Column1 INT NOT NULL
  ,Column2 CHAR(1) NOT NULL
  ,PRIMARY KEY(Column1,Column2)
  );

  INSERT INTO my_table VALUES
  (1       ,'A'),
  (1       ,'B'),
  (1       ,'C'),
  (2       ,'D'),
  (2       ,'A'),
  (2       ,'B'),
  (2       ,'E'),
  (2       ,'F'),
  (3       ,'A'),
  (3       ,'D'),
  (3       ,'C'),
  (3       ,'H'),
  (3       ,'J');

  SELECT column1
       , MAX(CASE WHEN rank = 1 THEN column2 END) n1
       , MAX(CASE WHEN rank = 2 THEN column2 END) n2
       , MAX(CASE WHEN rank = 3 THEN column2 END) n3
       , MAX(CASE WHEN rank = 4 THEN column2 END) n4
       , MAX(CASE WHEN rank = 5 THEN column2 END) n5
    FROM
       ( SELECT x.*
              , COUNT(*) rank 
           FROM my_table x 
           JOIN my_table y 
             ON y.column1 = x.column1 
            AND y.column2 <= x.column2 
          GROUP 
             BY x.column1
              , x.column2
       ) n
   GROUP 
      BY column1;

  +---------+------+------+------+------+------+
  | column1 | n1   | n2   | n3   | n4   | n5   |
  +---------+------+------+------+------+------+
  |       1 | A    | B    | C    | NULL | NULL |
  |       2 | A    | B    | D    | E    | F    |
  |       3 | A    | C    | D    | H    | J    |
  +---------+------+------+------+------+------+