Row as column in mysql without using function

Question

I am wodering is it possible to use a pivot table to make the col as rows without using any mysql functions and GROUP AS.

Here's the table

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 |    1 |    2 |    6 |
|  2 |    7 |    4 |    8 |
|  3 |    4 |    1 |    5 |
+----+------+------+------+

Here's the desired output iam looking for. The header is the id

+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 1 | 7 | 4 |
| 2 | 4 | 1 |
| 6 | 8 | 2 |
+---+---+---+

@Dexter . . . What could you possibly mean by "without using any mysql functions"? — Gordon Linoff
– Gordon Linoff, Commented Dec 24, 2019 at 13:51

Gordon Linoff · Accepted Answer · 2019-12-24 12:47:28Z

4

This is painful, but you can do:

select max(case when id = 1 then col end) as id_1,
       max(case when id = 2 then col end) as id_2,
       max(case when id = 3 then col end) as id_3       
from ((select id, col1 as col, 1 as which from t) union all
      (select id, col2 as col, 2 as which from t) union all
      (select id, col3 as col, 3 as which from t)
     ) ic
group by which;

Note that this only works for the data that you have provided. If you have additional columns or rows, you would need to modify the query.

Here is a db<>fiddle.

answered Dec 24, 2019 at 12:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

8 Comments

Strawberry Over a year ago

Are those not functions?

Dexter Over a year ago

@gordon Linoff this is great. will try it. Thanks

Gordon Linoff Over a year ago

@Strawberry . . . I actually interpreted that as "user-defined functions". But I totally get how that is interpreting something in the question that might not be there.

Dexter Over a year ago

@GordonLinoff I have 100 id's in the table and is it advisable to do the case for each individual id's? I can see why you said it is painful.

Gordon Linoff Over a year ago

@Dexter . . . You can generate the code using a spreadsheet or SQL query.

|

Collectives™ on Stack Overflow

Row as column in mysql without using function

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related