SQL Server 2005 以未知列数为中心 [英] SQL Server 2005 Pivot on Unknown Number of Columns

问题描述

我正在处理一组如下所示的数据.

<块引用>

学生姓名 |作业名称 |年级--------------------------------------学生A |作业 1 |100学生A |作业 2 |80学生A |总计 |180学生B |作业 1 |100学生B |作业 2 |80学生B |作业 3 |100学生B |总计 |280

任务的名称和数量是动态的，我需要得到类似于以下的结果.

<块引用>

学生 |作业 1 |作业 2 |作业 3 |全部的--------------------------------------------------------------------学生 A |100 |80 |空 |180学生 B |100 |80 |100 |280

现在理想情况下，我想根据可以包含/关联到每个作业的到期日期"对列进行排序.如果可能，总数应该放在最后(如果可能，可以计算并从查询中删除.)

我知道如何使用 pivot 来完成 3 个任务，只需命名列，它正在尝试以我还没有找到好的解决方案的动态方式来完成它.我正在尝试在 SQL Server 2005 上执行此操作

编辑

理想情况下，我想在不使用动态 SQL 的情况下实现这一点，因为这违反了政策.如果不可能……那么使用动态 SQL 的工作示例将起作用.

解决方案

我知道你说没有动态 SQL，但我看不出有什么办法可以直接 SQL.

如果您在 数据透视表和连接列和PIVOT in sql 2005

那里的动态SQL不容易被注入，也没有很好的理由禁止它.另一种可能性(如果数据不经常更改)是进行代码生成 - 而不是动态 SQL，SQL 定期生成到存储过程.

I am working with a set of data that looks something like the following.

StudentName  | AssignmentName |  Grade
---------------------------------------
StudentA     | Assignment 1   | 100
StudentA     | Assignment 2   | 80
StudentA     | Total          | 180
StudentB     | Assignment 1   | 100
StudentB     | Assignment 2   | 80
StudentB     | Assignment 3   | 100
StudentB     | Total          | 280

The name and number of assignments are dynamic, I need to get results simlilar to the following.

Student      | Assignment 1  | Assignment 2  | Assignment 3  | Total
--------------------------------------------------------------------
Student A    | 100           | 80            | null          | 180
Student B    | 100           | 80            | 100           | 280

Now ideally I would like to sort the column based on a "due date" that could be included/associated with each assignment. The total should be at the end if possible (It can be calculated and removed from the query if possible.)

I know how to do it for the 3 assignments using pivot with simply naming the columns, it is trying to do it in a dynamic fashion that I haven't found a GOOD solution for yet. I am trying to do this on SQL Server 2005

EDIT

Ideally I would like to implement this WITHOUT using Dynamic SQL, as that is against the policy. If it isn't possible...then a working example with Dynamic SQL will work.

解决方案

I know you said no dynamic SQL, but I don't see any way to do it in straight SQL.

If you check out my answers to similar problems at Pivot Table and Concatenate Columns and PIVOT in sql 2005

The dynamic SQL there is not vulnerable to injection, and there is no good reason to prohibit it. Another possibility (if the data is changing very infrequently) is to do code-generation - instead of dynamic SQL, the SQL is generated to a stored procedure on a regular basis.

这篇关于SQL Server 2005 以未知列数为中心的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！