当我不想使用聚合时，是否应该使用Pivot将SQL Server 2008行值转换为列名？

I am trying to get a handle on manipulating table data into more visually appealing formats for output. This could be part of the problem as what I want may be intended for separate reporting software.

我试图掌握将表数据操作为更具视觉吸引力的输出格式。这可能是问题的一部分，因为我想要的可能是用于单独的报告软件。

I have a table that looks like this

我有一张看起来像这样的桌子

teacher    student
----------------------
teacher1   Bob
teacher1   Jim
teacher2   Sam
teacher3   Bill
teacher3   John
teacher3   Eric

I want a table that looks something like this:

我想要一个看起来像这样的表：

teacher1    teacher2    teacher3
---------------------------------
Bob         Sam          Bill
Jim         null         John
null        null         Eric

So I tried stuffing all the teacher names in a variable and then using a Pivot but since I have to choose an aggregate I can only get the Max or Min student like this:

所以我尝试在变量中填充所有教师名称，然后使用Pivot，但由于我必须选择一个聚合，我只能得到这样的Max或Min学生：

DECLARE @teacherList AS VARCHAR(max)

SELECT @teacherList = Stuff((SELECT DISTINCT',[' + teacher + ']'
                              FROM myTable
                              FOR xml path('')), 1, 1, '')

DECLARE @dynamic_pivot_query AS VARCHAR(max)

SET @dynamic_pivot_query = 'select' + @teacherList + 
'from 
(
    SELECT [teacher],[student]
    FROM [dbo].[myTable]
) as S
Pivot
(
    MIN([student])
    FOR teacher IN (' + @teacherList + ')
) as P
'
EXEC(@dynamic_pivot_query)

The result of this is:

结果是：

teacher1    teacher2    teacher3
---------------------------------
Bob         Sam          Bill

Assuming the following:

假设如下：

# of teachers and their names are unknown (variable)
教师的数量及其名称未知（可变）
# of students per teacher is unknown and likely different for every teacher
每位教师的学生人数不详，每位教师可能会有所不同

Is there a way to do this?

有没有办法做到这一点？

2 个解决方案

#1

You can use row_number to get the result you want.

您可以使用row_number来获得所需的结果。

SET @dynamic_pivot_query = 'select ' + @teacherList + 
'from 
(
    SELECT [teacher],[student], row_number() over(partition by teacher order by student) as rn
    FROM [dbo].[myTable]
) as S
Pivot
(
    MIN([student])
    FOR teacher IN (' + @teacherList + ')
) as P
'

Update:
To remove the SQL Injection vulnerability you should use quotename to properly quote your field list.

更新：要删除SQL注入漏洞，您应该使用引号来正确引用您的字段列表。

SELECT @teacherList = Stuff((SELECT DISTINCT',' + quotename(teacher)
                              FROM myTable
                              FOR xml path('')), 1, 1, '')

#2

No.

没有。

SQL Server requires static typing. There is no way to create a dynamic number of columns or dynamic column types (except for sql_variant).

SQL Server需要静态类型。无法创建动态数量的列或动态列类型（sql_variant除外）。

Therefore your dynamic SQL solution is the only possible choice.

因此，您的动态SQL解决方案是唯一可行的选择。

Don't let min/max confuse you: There will always be exactly 0 or 1 item per aggregation. The syntax requires an aggregate for theoretical correctness, but if (teacher, student) is unique the aggregate is doing nothing. It does no harm and it does not alter the results.

不要让min / max混淆你：每个聚合总会有0或1项。语法需要聚合用于理论上的正确性，但如果（教师，学生）是唯一的，则聚合无效。它没有坏处，也没有改变结果。

The approach is right just the way it is. Actually, I am working on the same type of code right now in this minute (which is funny).

这种方法恰到好处。实际上，我现在正在研究相同类型的代码（这很有趣）。

#1