Subscribe to Youtube channel

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
 (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:

      SELECT DISTINCT id, '[' + Description_En + ']' AS Description_En
      FROM LKUP_Subject
            @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 = ''

FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName
   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

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
 (SELECT StudentId, Grade, Description_En
 FROM LKUP_Subject
  INNER JOIN Student_Subject
   ON LKUP_Subject.Id = Student_Subject.SubjectId) S
  SUM (Grade)
  FOR Description_En IN
  (Physics, Chemistry, Math, English)) AS pvt

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


Popular posts from this blog

Implement Logging in CSV file using Nlog in .net core MVC application- part 2

Implement Nlog in .Net core MVC application part 1

Disable backspace key using Jquery

Devexpress Datebox date formatting in angular 6 with example

Angular User Session Timeout example step by step

Restore the lost focus of Auto post back controls in update Panel control

How to Import CSV File and bind csv file data to gridview in using c#

Remove Owin from MVC 5 Application and use custom forms authentication

Configure archival and purging of log file in Nlog