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

what have you tried, please show us the code that you are having trouble with so we can help you improve it or enhance it. — Our Man in Bananas
– Our Man in Bananas, Commented Aug 7, 2014 at 13:49
@Philip To be fair they are asking whether a query exists that will do it, not for help with the query. — Arth
– Arth, Commented Aug 7, 2014 at 13:51
You cannot (legally) order the results in the way that you have suggested, because you have not made explicit (within your design) the fact that '2,D' precedes '2,A'... and why would you want them in separate columns? — Strawberry
– Strawberry, Commented Aug 7, 2014 at 14:11
@Strawberry The order does not particularly matter. I just need them displayed in separate columns because the table will need to be manipulated by other users (in Excel). Arth commented that unless I can order the data by As then Bs then Cs it would be easier; however I am unable to do so because the values in column2 vary quite a lot. — user3918629
– user3918629, Commented Aug 7, 2014 at 14:32
Do you know for sure that there won't be more than 5 values for row? — Strawberry
– Strawberry, Commented Aug 7, 2014 at 14:39

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 7, 2014 at 14:18

John Ruddell

25.9k7 gold badges60 silver badges88 bronze badges

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

11 Comments

user3918629 Over a year ago

Hey John, so you're query almost worked! But for some reason, it is only returning some of the values linked to each ID as opposed to all of them. Why would that be?

John Ruddell Over a year ago

@user3918629 without seeing your data I couldn't tell you.. try running the inner query with the calculations for the counter.. see what it returns... make sure that the counter increments for each duplicate id and then resets to 1 for the next id.. you may need to order the results by the id first in a subquery.. just depends on whats happening

user3918629 Over a year ago

it seems that the counter only goes up to three (which is in line with my question) but in some cases there are more than that. I don't see why the counter would stop at 3, but for some reason it is...

John Ruddell Over a year ago

@user3918629 you have this set up incorrectly then because in my example the counter goes up to 5 because there are 5 duplicate records. you need to post the exact query

user3918629 Over a year ago

I did not change anything from the query you sent me. However, even in the case where there are 5 duplicate records, the query only returns 3 of the 5.

|

Arth · Accepted Answer · 2014-08-07 13:55:43Z

4

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

edited Aug 7, 2014 at 13:55

answered Aug 7, 2014 at 13:47

Arth

13.1k5 gold badges43 silver badges72 bronze badges

17 Comments

VMai Over a year ago

It's not ordered in OPs sample result, but without a suitable column the result will be unpredictable but by your second solution.

user3918629 Over a year ago

Sorry this is my first time posting so I'm not comfortable with the formatting! I need the results to be displayed in individual columns as well. So for the first row, entries 'A', 'B' and 'C' would each be in its own column.

Arth Over a year ago

@VMai, The second solution orders the concatenation by the value of column2 in the db.. or is that what you mean?

VMai Over a year ago

@user3918629 That is not easily done - especially without a suitable column that guarantees a certain order of the rows. Please consider the rows of a table as an unordered set.

VMai Over a year ago

@user3918629 From the Related section on the right sidebar: stackoverflow.com/questions/9685601/… Please consider to mention such requirements in the question. You could have given every column a header. That would helped your helpers enormously.

|

Strawberry · Accepted Answer · 2014-08-07 14:43:25Z

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    |
  +---------+------+------+------+------+------+

answered Aug 7, 2014 at 14:43

Strawberry

34k14 gold badges43 silver badges58 bronze badges

1 Comment

John Ruddell Over a year ago

you can add an ELSE ' ' in your case to mimic the OP's desired result :) but this is a good solution for sorted results by id

Collectives™ on Stack Overflow

MySQL Transpose one column based on group

3 Answers 3

NOTE:

QUERY:

RESULTS:

11 Comments

17 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

NOTE:

QUERY:

RESULTS:

11 Comments

17 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related