如何翻转/重组从SQL select语句检索的数据? [英] How to flip/reorganize the data retrieved from an SQL select statement?

问题描述

现在，我有一个SQL查询，该查询以以下方式返回数据:

Right now, I have an SQL query that returns data in the following way:

"SCHOOL"和"TEACHER"列中都有重复的值，例如A学校和C学校都有名叫史密斯女士的老师.

There are repeating values in both the "SCHOOL" and the "TEACHER" columns, e.g. School A and School C both have teachers named Ms. Smith.

我需要以这种方式显示我的数据:

I need to present my data in this way instead:

是否可以直接在SQL select语句中执行类似的操作(如果不可能，则使用或不使用动态列名)?我对每种方法的处理方式有何不同?请给我一些例子吗?

Is it possible to do something like this directly in the SQL select statement (with or without the dynamic column names, if that's impossible)? What are the differences in how I would go about each approach? Could I get some examples, please?

我原来的select语句非常复杂，我不想发布它，因为这个问题与其他任何人都没有关系.所以我总体上问，如何去做呢?

My original select statement is very complex and I don't want to post it since this problem then wouldn't be relatable to anyone else. So I am asking in general, how would one go about doing this?

推荐答案

如果使用的是SQL Server 2005或更高版本，则可以使用PIVOT运算符.像这样:

If you're using SQL Server 2005 or later, you could use the PIVOT operator. Something like this:

SELECT
    School,
    [Ms. Smith],
    [Mr. Rogers],
    [Mr. Berkenheim],
    [Ms. Roberts],
    [Mr. Ashby],
    [Ms. Robinson]
FROM <data_table> AS D
PIVOT (
    SUM(NumberOfStudents) FOR Teacher IN ([Ms. Smith],[Mr. Rogers],[Mr. Berkenheim],[Ms. Roberts],[Mr. Ashby],[Ms. Robinson])
) P

这篇关于如何翻转/重组从SQL select语句检索的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！