How to convert rows into columns in Sql Server 2008

Convert rows into columns in sql server database? We're going to see how to do that using different query, and there is the "Pivot" function in SQL Server 2005.
 

 
Example: We are using two 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 can use the "case"  statement in the query to convert rows into columns.

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 can convert rows into columns dynamically using cursor:

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 called PIVOT that convert rows into columns in a single step:

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

 
Hope this will helpful for you. Happy coding....