例如，我有一个名为“ stdinfo”的表，例如
Id Name Mark
-----------------
1 Helal 10
2 Shakil 15
... ... ...
... ... ...
... ... ...
现在我想转换表结果如下
Name Helal Shakil ...
----------------------
Id 1 2 ...
Mark 10 15 ...
重要的是行数不是固定的。
请任何人帮助我编写sql查询以获得此结果。
先谢谢了...
最佳答案
You can try this,
Table
--------------------------------------------------
subject_id subjectname classid teacher_id
---------------------------------------------------
3 Math 3 T-1
4 Economics 4 T-1
5 Physcis 3 T-1
--------------------------------------------------
Query:
SELECT sum( if( subjectname = 'Math', subject_id, 0 ) ) AS 'Economics',
sum( if( subjectname = 'Economics', subject_id, 0 ) ) AS 'Math',
sum( if( subjectname = 'Physcis', subject_id, 0 ) ) AS 'Physcis'
FROM tbsubject group by subjectname
Output:
----------------------------
Math Economics Physcis
---------------------------
0 4 0
3 0 0
0 0 5
----------------------------
Or you can create a view by programming.more example see pivot table
http://www.krishnasunuwar.com.np/2011/02/crosstab-query-pivot-table-or-transformation-of-rows-into-columns/