如何翻转/重新组织从SQL select语句中检索的数据？(How to flip/reorganize the data retrieved from an SQL select statement

如何翻转/重新组织从SQL select语句中检索的数据？(How to flip/reorganize the data retrieved from an SQL select statement?)

现在，我有一个SQL查询，它以下列方式返回数据：

“学校”和“教师”栏目中都有重复的值，例如，学校A和学校C都有名为史密斯女士的教师。

我需要以这种方式呈现我的数据：

是否有可能直接在SQL select语句中执行此类操作（如果不可能，可以使用或不使用动态列名）？ 我对每种方法的看法有何不同？ 我能得到一些例子吗？

我原来的选择语句非常复杂，我不想发布它，因为这个问题不会与其他任何人相关。 所以我一般都会问，怎么会这样做呢？

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

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:

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?

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运算符。 像这样的东西：

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

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