How to Pivot Data with an Unknown Number of Columns in SQL Server 2005?

Pivot Data Handling Unknown Number of Columns in SQL Server 2005

This article explores the pivoting challenges of handling data with an unknown number of columns in SQL Server 2005. The given data set contains columns of variables representing assignments and grades, and the desired output is a pivot table containing columns for assignments and total student scores.

Due to SQL Server 2005 limitations, a pure SQL (no dynamic SQL) solution is not possible for this task. As mentioned in the suggested answer, dynamic SQL provides a way to dynamically generate the necessary statements based on the current data.

For example, if a table named Assignments contains assignment names and due dates:

<code class="language-sql">CREATE TABLE Assignments (
  ID int NOT NULL,
  AssignmentName varchar(50) NOT NULL,
  DueDate datetime
);</code>

and data:

<code class="language-sql">INSERT INTO Assignments (ID, AssignmentName, DueDate) VALUES (1, 'Assignment 1', '2023-03-01'), (2, 'Assignment 2', '2023-04-01'), (3, 'Assignment 3', '2023-05-01');</code>

The required output can be generated using the following dynamic SQL:

<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SET @cols = (SELECT STUFF((
  SELECT ',[' + AssignmentName + ']'
  FROM Assignments
  FOR XML PATH(''), TYPE
), 1, 1, ''));

SET @query = 'SELECT
  StudentName,
  ' + @cols + ',
  SUM(Grade) OVER (PARTITION BY StudentName) AS Total
FROM
  (SELECT
    StudentName,
    AssignmentName,
    CASE WHEN AssignmentName = ''Total'' THEN NULL ELSE Grade END AS Grade
  FROM
    YourTable) AS t
PIVOT
  (MAX(Grade)
  FOR AssignmentName IN (' + @cols + ')) AS pvt';

EXECUTE(@query);</code>

This will create a pivot table with the assignment column and student total score, sorted by due date (if the DueDate column exists in the table).

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