How to Dynamically Pivot Data with Unknown Columns in SQL Server 2005?

SQL Server 2005 Dynamic Pivot

In SQL Server 2005, pivot operations with an unknown number of columns can be tricky. This article explores solutions to such problems.

Question:

How to dynamically pivot data with unknown number of columns in SQL Server 2005? The desired output should resemble a table with unique students as rows, dynamic columns representing assignments, and sorted by due date. If possible, the "Total" column should appear last.

Answer:

Due to limitations of SQL Server 2005, it may be necessary to use dynamic SQL to implement dynamic pivots. One possible solution is to leverage dynamic management views (DMVs) and XML to generate dynamic SQL statements.

DECLARE @DynamicSQL NVARCHAR(MAX) = N'';

-- 从表中获取不同的作业名称
SELECT DISTINCT AssignmentName
INTO #AssignmentNames
FROM TableName;

-- 循环遍历作业名称
SELECT @DynamicSQL = @DynamicSQL + '
  MAX(CASE WHEN AssignmentName = ''' + AssignmentName + ''' THEN Grade END) AS ' + AssignmentName + ','

FROM #AssignmentNames;

-- 删除动态 SQL 语句末尾的逗号
SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 1);

-- 构造用于透视的最终动态 SQL 语句
SET @DynamicSQL = 'SELECT StudentName, ' + @DynamicSQL + '
  FROM TableName
  GROUP BY StudentName;';

-- 执行动态 SQL 语句
EXEC sp_executesql @DynamicSQL;

This method generates a dynamic SQL statement that contains the columns required to pivot based on the different job names identified in the table.

Note:

Although this solution involves dynamic SQL, it is not vulnerable to injection attacks because the SQL statements are constructed from known and trusted data. Alternatively, if the data changes frequently, consider implementing code generation to create stored procedures with the required SQL statements.

The above is the detailed content of How to Dynamically Pivot Data with Unknown Columns in SQL Server 2005?. For more information, please follow other related articles on the PHP Chinese website!