How to convert row to column in MySQL

Question

I am trying to dynamically convert rows to columns in MySQL.

Table name is student_det.

ID    Roll_No         Q.No
1      21345         100
2      21345         102
3      21345         103
4      21346         100
5      21346         144
6      21567         233
7      21567         455

**Ans should be like** 

    Roll_no Qno1    Qno2    Qno3
    21345   100      102    103
    21346   100      144    null
    21567   233      455    null
  ----------

The code I use to convert rows to columns, is the following

Select roll_no,Q.No1, Q.No2,Q.No3
From
(
  Select Roll_No, Q.No,
    'Q.No'+
      CAST(row_number() over(partition by Roll_No order by Roll_No) 
     as varchar(10)) ColumnSequence
  from student_det
) as Temp
PIVOT
(
  MIN(Q.No)
  FOR ColumnSequence IN(Q.No1, Q.No2,Q.No3)
) as Piv

Why would need to create such a table result? Its not clear what your intention is for this, so maybe [group concat](https://stackoverflow.com/questions/2516545/mysql-group-concat) could help? You added the PHP tag which has nothing to do with this question, if you use this data later on in PHP then you could easily create it through the first result. — Definitely not Rafal, Jun 18 '21 at 09:57

score 1 · Accepted Answer · answered Jun 18 '21 at 09:34

Unfortunately, MySQL does not have PIVOT function, so in order to rotate data from rows into columns you will have to use a CASE expression along with an aggregate function.

Schema and insert statements:

 create table student_det(ID int,   Roll_No int, QNo int);
 insert into student_det values(1      ,21345,         100);
 insert into student_det values(2     , 21345 ,        102);
 insert into student_det values(3    ,  21345  ,       103);
 insert into student_det values(4   ,   21346   ,      100);
 insert into student_det values(5  ,    21346    ,     144);
 insert into student_det values(6 ,     21567     ,    233);
 insert into student_det values(7,      21567      ,   455);

Query:

 with cte as 
 (
   Select Roll_No, QNo,
       row_number() over(partition by Roll_No order by Roll_No) ColumnSequence
   from student_det
 )
 select roll_no,
        max(case when columnsequence=1 then QNo end)Qno1,
        max(case when columnsequence=2 then QNo end)Qno2,
        max(case when columnsequence=3 then QNo end)Qno3
 from cte       
 group by roll_no

Output:

roll_no	Qno1	Qno2	Qno3
21345	100	102	103
21346	100	144	null
21567	233	455	null

db<fiddle here

How to convert row to column in MySQL

1 Answers1