mysql - 如何对MySQL表数据和列进行转置操作？

Question

例如，我有一个名为“stdinfo”的表

Id   Name    Mark
-----------------
1    Helal   10
2    Shakil  15
...  ...     ...
...  ...     ...
...  ...     ...

现在我想像下面这样转换表格结果

Name  Helal Shakil ...
----------------------
Id    1     2      ...
Mark  10    15     ...
   

有一点很重要，就是行数不固定。

Answer 1

你可以试试这个

表格

--------------------------------------------------
subject_id    subjectname   classid  teacher_id
---------------------------------------------------
3           Math         3          T-1
4         Economics      4          T-1
5          Physcis       3          T-1
--------------------------------------------------

查询:

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

输出:

----------------------------
Math   Economics  Physcis
---------------------------
0   4       0
3   0       0
0   0       5
----------------------------

或者您可以通过编程创建 View 。更多示例请参见数据透视表 http://www.krishnasunuwar.com.np/2011/02/crosstab-query-pivot-table-or-transformation-of-rows-into-columns/