How to convert rows into columns in Sql Server 2008

How to convert rows into columns? We're going to see how to do that using query, and there is the "Pivot" function in SQL Server 2005.
The easiest way is when you know the columns which are fixed. But most of the times, you want to do that in general not for specific columns.

If you have fixed columns, then all you will be doing is an “if-else” field that holds the column names and set them into different columns:
Example: We have 2 tables: Subject which is a lookup table that holds the subject names, and the StudentSubject which contains the student grades for the different subject.



We are going to build the query having fixed columns:

We use the "if-else" functionality in the query to put them in the defined columns (in SQL, it is "case"):


SELECT StudentId,
 SUM(Physics) AS Physics,
 SUM(Chemistry) As Chemistry,
 SUM(Math) AS Math,
 SUM(English) As English
FROM
 (SELECT StudentId,
  (CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics,
  (CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry,
  (CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math,
  (CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English
 FROM Student_Subject) s
GROUP BY StudentId



Now, we will build it dynamically using cursor (you can do it using temporary tables to do the same functionality for performance reasons):


DECLARE Cur CURSOR FOR
      SELECT DISTINCT id, '[' + Description_En + ']' AS Description_En
      FROM LKUP_Subject
DECLARE @SubjectName NVARCHAR(MAX),
            @SubjectId INT,
   @Sum NVARCHAR(MAX), -- The SUM part of the query
            @Select NVARCHAR(MAX), -- The inner query 
   @Sql NVARCHAR(MAX) -- The total sql statement
SET @Select = ''
SET @Sum = ''
SET @Sql = ''
OPEN Cur

FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @Sum = @Sum + 'SUM(' + @SubjectName + ') AS ' + @SubjectName + ','
      SET @Select = @Select + '(CASE WHEN SubjectId = ' + CONVERT(NVARCHAR(10), @SubjectId) + ' THEN Grade END) AS ' + @SubjectName + ','
      FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName
END
CLOSE Cur
DEALLOCATE Cur

IF RIGHT(@Select, 1) = ','
      SET @Select = STUFF(@Select, LEN(@Select), 1, ' FROM Student_Subject')
IF RIGHT(@Sum, 1) = ','
 SET @Sum = STUFF(@Sum, LEN(@Sum), 1, '')
SET @Sql = 'SELECT StudentId, ' + @Sum + ' FROM (SELECT StudentId, ' + @Select + ') s GROUP BY StudentId'
EXEC sp_executesql @Sql



In SQL Server 2005, there is a new feature that does all of this in a single step: PIVOT

In fixed columns, here is how we use it:


SELECT StudentId, Physics, Chemistry, Math, English
FROM 
 (SELECT StudentId, Grade, Description_En
 FROM LKUP_Subject
  INNER JOIN Student_Subject
   ON LKUP_Subject.Id = Student_Subject.SubjectId) S
 PIVOT
 (
  SUM (Grade)
  FOR Description_En IN
  (Physics, Chemistry, Math, English)) AS pvt



Note: You should use an aggregate function like the SUM (for the same reason as you should use an aggregate function when using the query to transform Rows to Columns)

As for how to do it dynamically:


DECLARE Cur CURSOR FOR
  SELECT DISTINCT Description_En
  FROM LKUP_Subject
DECLARE @Temp NVARCHAR(MAX),
  @AllSubjects NVARCHAR(MAX),
  @SubjectQuery NVARCHAR(MAX)
SET @AllSubjects = ''
OPEN Cur
-- Getting all the subjects
FETCH NEXT FROM Cur INTO @Temp
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @AllSubjects = @AllSubjects + '[' + @Temp + '],'
 FETCH NEXT FROM Cur INTO @Temp
END
CLOSE Cur
DEALLOCATE Cur
SET @AllSubjects = SUBSTRING(@AllSubjects, 0, LEN(@AllSubjects))
-- Building the pivot query
SET @SubjectQuery = 'SELECT StudentId, ' + @AllSubjects + '
FROM 
(SELECT StudentId, Grade, Description_En
FROM LKUP_Subject
 INNER JOIN Student_Subject
  ON LKUP_Subject.Id = Student_Subject.SubjectId) S
PIVOT
(
SUM (Grade)
FOR Description_En IN
(' + @AllSubjects + ')) AS pvt'
EXEC sp_executesql @SubjectQuery



Hope this will helpful for you. Happy coding

Comments