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

Question

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

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

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

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

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

所以我尝试将所有教师姓名填充到一个变量中，然后使用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)  

其结果是：

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

假设以下情况：

1. # 教师及其姓名未知（可变）
2. # 每位教师的学生人数未知，每位教师可能不同

有没有办法做到这一点？

Answer 1

No.

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

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

不要让最小/最大混淆您：每个聚合总是有 0 或 1 个项目。语法需要一个聚合来保证理论的正确性，但如果（教师、学生）是唯一的，则聚合什么都不做。它没有伤害，也不会改变结果。

这种方法是正确的。实际上，我现在正在处理相同类型的代码（这很有趣）。

Answer 2

您可以使用

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
'

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

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