In this post, I will share a cheap trick technique for pivoting a variable number of columns using Microsoft’s Transact-SQL. The idea of writing dynamic SQL to accomplish a pivot is nothing new, and can be found in discussion forums all over the web. Anyway, I’ve been looking for something to blog about, so I thought I’d go into more detail with this.
You’re given two tables
Courses. They have the keys
COURSE_ID, respectively. Both tables are subject to frequent updates, inserts and deletes.
A third table called
Grades stores the letter-value of the grade scored by each student, in each course that he took. A record in Grades is structured somewhat like this: (
STUDENT_ID | COURSE_ID | GRADE).
What is required now, is a table or a view Report that has exactly one row for each student in Students, and a column for each of the courses in Courses, with the intersection of a row and a column denoting the grade received by a certain student in a certain course.
While this would have been easy had Courses been a static table, implementing a pivot involving a variable number of pivot columns would require more than just your average T-SQL query. And that is because your average T-SQL query would require the number of pivot columns to be known at the time of writing the query.
To put it plainly, what you do is engineer your
PIVOT query by running a loop once for each pivot column, and piecing together the parts. Then you pass this query to
EXECUTE(). Additionally, if the results of the
PIVOT are to go inside a table, then this table will have to be created using a
CREATE TABLE query, also built brick-by-brick inside a loop. Here’s what your code would look like, more or less.
--Build a query to create the table 'Report' IF OBJECT_ID("Report") IS NOT NULL DROP TABLE Report DECLARE @create_query VARCHAR(MAX) SET @create_query = "CREATE TABLE [Report] ( [STUDENT_NAME] VARCHAR(50)" --Create a temp table to store the names and IDs of the pivot columns. --The INSERT query here controls the order, names and formatting of the --pivot columns IF OBJECT_ID("tempdb..#temp_Courses") IS NOT NULL DROP TABLE #temp_Courses CREATE TABLE #temp_Courses([COURSE_ID] VARCHAR(10), [COURSE_NAME] VARCHAR(50), [ROW_NUM] BIGINT); INSERT INTO #temp_Courses SELECT *, ROW_NUMBER() OVER (ORDER BY [COURSE_NAME] ASC) AS [ROW_NUM] FROM Courses; --Get the count of pivot columns; this will be used in our loop below DECLARE @column_count INT; SET @column_count = (SELECT COUNT(*) FROM #temp_Courses); --We'll build two parts of our PIVOT query separately, and add them together later --(A look at the syntax of a PIVOT query will reveal why this was needed) DECLARE @pivot_query VARCHAR(MAX); DECLARE @column_id_list VARCHAR(MAX); SET @pivot_query = "SELECT [STUDENT_NAME]"; SET @column_id_list = ""; DECLARE @row_number INT; SET @row_number = 1; DECLARE @column_id VARCHAR(10) DECLARE @column_name VARCHAR(50) --Loop through the records in Courses, putting together your PIVOT query piece-by-piece WHILE @row_number <= @column_count BEGIN SET @column_id = (SELECT COURSE_ID FROM #temp_Courses WHERE ROW_NUM = @row_number); SET @column_name = (SELECT COURSE_NAME FROM #temp_Courses WHERE ROW_NUM = @row_number); SET @create_query = @create_query + ", [" + @column_name + "] VARCHAR(2)"; SET @pivot_query = @pivot_query + ", [" + @column_id + "] AS [" + @column_name + "]"; SET @column_id_list = @column_id_list + CASE WHEN @row_number = 1 THEN "" ELSE ", " END + "[" + @column_id + "]"; SET @row_number = @row_number + 1; END SET @create_query = @create_query + ");" SET @pivot_query = @pivot_query + " FROM ( SELECT G.STUDENT_ID [STUDENT_ID], S.STUDENT_NAME [STUDENT_NAME], G.COURSE_ID [COURSE_ID], G.GRADE [GRADE] FROM Grades G LEFT OUTER JOIN Students S ON G.STUDENT_ID = S.STUDENT_ID ) J PIVOT ( MAX([GRADE]) FOR [COURSE_ID] IN (" + @column_id_list + ") ) AS P;"; --Drop the temp table IF OBJECT_ID("tempdb..#temp_Courses") IS NOT NULL DROP TABLE #temp_Courses --Execute the pivot-query EXECUTE(@create_query + "INSERT INTO [Report] " + @pivot_query)
The generated SQL queries look like this:
CREATE TABLE [Report] ( [STUDENT_NAME] VARCHAR(50), [Algorithms: Design and Analysis, Part 1] VARCHAR(2), [Critical Thinking in Global Challenges] VARCHAR(2), [Developing Innovative Ideas for New Companies] VARCHAR(2), [E-learning and Digital Cultures] VARCHAR(2), [India`s Rivers] VARCHAR(2), [Intermediate Algebra] VARCHAR(2), [Introduction to Philosophy] VARCHAR(2), [Nutrition for Health Promotion] VARCHAR(2), [Pre-Calculus] VARCHAR(2), [Principles of Public Health] VARCHAR(2) ); INSERT INTO [Report] SELECT [STUDENT_NAME],  AS [Algorithms: Design and Analysis, Part 1],  AS [Critical Thinking in Global Challenges],  AS [Developing Innovative Ideas for New Companies],  AS [E-learning and Digital Cultures],  AS [India`s Rivers],  AS [Intermediate Algebra],  AS [Introduction to Philosophy],  AS [Nutrition for Health Promotion],  AS [Pre-Calculus],  AS [Principles of Public Health] FROM ( SELECT G.STUDENT_ID [STUDENT_ID], S.STUDENT_NAME [STUDENT_NAME], G.COURSE_ID [COURSE_ID], G.GRADE [GRADE] FROM Grades G LEFT OUTER JOIN Students S ON G.STUDENT_ID = S.STUDENT_ID ) J PIVOT ( MAX([GRADE]) FOR [COURSE_ID] IN (, , , , , , , , , ) ) AS P;
But Watch Out!
Column names used in the pivot query should not exceed the SQL Server limit of 128 characters. Like in the above query, if any course name were to exceed 128 characters, the
EXECUTE would fail. This is a limitation imposed by SQL Server.