cross tab query in mysql

[mtvaran]

hi guys, i am  struggling with cross tab query in mysql. if you have any idea could you please help me? basically i have data into the table like...

 

cid | Q# | marks

c1 | 1| 50

c1 | 2 | 50

c1 | 3 | 50

 

but i need to be the table like...

 

cid | Q1 | Q2 | Q3

c1 | 50 | 50 | 50

[fenway]

That requires joining the table to itself as many times as you has Qs.

[DavidAM]

You can accomplish it using GROUP BY but you still need to know how many Q's you have:

 

SELECT cid, 
       SUM(IF(`Q#`=1, marks, 0)) AS Q1,
       SUM(IF(`Q#`=2, marks, 0)) AS Q2,
       SUM(IF(`Q#`=3, marks, 0)) AS Q3
FROM tableName
GROUP BY cid

 

You could use PHP and another query to determine the number of Q's and dynamically build the SELECT statement.