现在,我有一个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]) ) PIf 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更多推荐
发布评论