SQL查询字段作为列 [英] SQL Query fields as columns

问题描述

我真的不知道该如何放置，但是请检查以下详细信息.

I dont really know how to put this but please kindly check the details below.

学生

|Student_ID|Student_Name|
|1         |Ryan        |
|2         |Camille     |
|3         |George      |

成绩

|Student_ID|Subject |Grade
|1         |Math    |5 
|1         |English |3 
|1         |History |1
|2         |Math    |3 
|2         |English |4 
|2         |History |1 
|3         |Math    |5 
|3         |English |1 
|3         |History |2 

有可能得到这个结果吗?

Is it possible to get this result?

Student_Name|Math|English|History
Ryan        |5   |3      |1
Camille     |3   |4      |1
George      |5   |1      |2

现在，通过使用列名，然后是学生名，然后为每个学生名添加详细信息来填充未绑定的数据网格，我一直在努力地做到这一点.这很耗时，我想更好地优化查询.

Now I've been doing this the hardway by populating an unbound datagrid with first the column name, then the student name then adding the the details for each student name. This is time consuming and I want to optimize the query better.

谢谢.

推荐答案

如果您知道的主题数很多，则@John的答案会起作用，如果您的主题数未知，那么您可以使用准备好的语句动态生成此信息.这是一篇好文章:

While @John's answer will work if you have a known number of subjects, if you have an unknown number of subjects then you can use prepared statements to generate this dynamically. Here is a good article:

动态数据透视表(将行转换为列)

您的代码如下:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when Subject = ''',
      Subject,
      ''' then Grade end) AS ',
      Subject
    )
  ) INTO @sql
FROM grade;

SET @sql = CONCAT('SELECT s.Student_name, ', @sql, ' 
                   FROM student s
                   LEFT JOIN grade AS g 
                    ON s.student_id = g.student_id
                   GROUP BY s.Student_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

请参见带有演示的SQL小提琴

这篇关于SQL查询字段作为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！