Salim Fayad

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.

Using Query:

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: LKUP_Subject which is a lookup table that holds the subject names, and the Student_Subject which contains the student grades for the different subject.

We are going to build the query having fixed columns:

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

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

              

 

2.       Then we use the “SUM” function to merge the results in 1 row like we want:

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

                            

Using Pivot:

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: that you should use an aggreate function like the SUM (for the same reason as you should use an aggreate 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

 

One nice thing is to be able to check and to query the schema of your database through writing simple queries and not through code.

For example, if you want to change a table structure or delete it, you want to find all the SPs and Views that use this table. Another example, if you want to check for schema changes between 2 databases, you can do that using simple queries. Another example, if you want to get the script of stored procedure of view using queries (for example writing a T-SQL to get you the script of some stored procedures)

 We are going to talk in this blog on 4:

1. INFORMATION_SCHEMA.TABLES: This is a system view that allows to see all the tables in your database

2. INFORMATION_SCHEMA.COLUMNS: This is also a system view that allows to see all the columns along with their metadata in your database

3. INFORMATION_SCHEMA.ROUTINES: This is also a system view that will get you all the stored procedures in your database

4. sys.sql_modules: This is a system table that stores the script of the stored procedures in your database.

         Example: SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.InsertStudent');

         This will get you the script of the stored procedure "InsertStudent"