更新时间:2023-12-01 09:48:04
我知道你说没有动态 SQL
,但我看不出有什么方法可以直接用 SQL代码>.
如果您在 数据透视表和连接列和sql 2005中的PIVOT
动态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.