Rows to Columns

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

 

43 Comments

  • Very nice article, really helped me to solve the problem..

    Thanks

  • Thanks for the nice post Salim. It helped me a lot.

  • This is fantastic, one of the best explanations of this requirement and solution

  • very nice work dude........

  • The different approaches are excellent and beautifully explained.

  • In order for you to do the same for a string, you have to follow a different approach:

    Declare @Strings varchar(MAX)

    SELECT @Strings = ISNULL(@Strings + ',', '') + StringCol
    FROM Table

    SELECT @Strings

  • Nice work, It helped me a lot .

    Thanks

  • Very Nice Job !!! I was very helpfull

  • That was great. Thanks

  • i want to create the table and i need to pass the parameter @allitems as a column name..please help me
    Example:
    alter procedure result0 as
    begin
    declare cur cursor for
    select distinct item from tbl_tempparent1 where item like'20%'or item like '19%'
    insert into tbl_audit(Auditname) select distinct Auditname from tbl_result
    DECLARE @Temp NVARCHAR(MAX),
    @Allitems NVARCHAR(MAX),
    @query NVARCHAR(MAX)

    SET @Allitems = ''
    OPEN Cur
    -- Getting all the items
    FETCH NEXT FROM Cur INTO @Temp
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @Allitems = @Allitems + '[' + @Temp + '],'
    FETCH NEXT FROM Cur INTO @Temp
    END
    --select substring(@Allitems,0,len(@Allitems))

    CLOSE Cur
    DEALLOCATE Cur
    --
    SET @Allitems = SUBSTRING(@Allitems, 0, LEN(@Allitems))

    -- Building the pivot query
    set @query='
    declare @audit varchar(100)
    declare @count int
    loop:
    select distinct top 1 @audit=Auditname from tbl_audit
    SELECT [MarketDefinition],[ByMarket],[NetPrice],' + @Allitems + ',[grandparent]
    FROM
    (SELECT a.MarketDefinition,a.ByMarket,a.NetPrice,b.item,b.netpric ,b.grandparent from tbl_tempparent1 as b
    join tbl_Market_Definition as a on b.grandparent=@audit and a.auditname=@audit)s
    pivot( max(netpric) for item in (' + @Allitems + '))as pvt
    delete from tbl_audit where id=(select min(id) from tbl_audit)
    select @count=count(*) from tbl_audit
    if(@count>0)
    begin
    goto loop;
    end
    --select case when ' + @Allitems + ' ''null ''then ' + @Allitems + ' end'

    EXEC sp_executesql @query

    end

  • Directly from Portugal I Would like to thanks you for the examplo with cursor/dynamically.
    Great help!

    Thanks!

  • Really the same which i expect, very nice and useful,

  • Thanx for Nice query...can u please help me doing same query in LINQ .... i m finding difficulty to perform Pivot/CrossTab in Linq....waiting for your Reply

    Thaks

  • This is the most simplest and best example I came across. I was frantically searching for this kind of solution.
    The examples used are simple and easy to understand.I was a great help, hence stopping buy to thank the author.
    thanks a ton...

  • Just perfect. Thanks a lot!!!

  • thanks for your code , it helped me a lot when i was stuck in one query

  • great job buddy. Helped me a lot...

  • -----------------------------------------------------------
    "I commonly really don't article in Blogs but your web site forced me to, amazing operate.!!! stunning …"

  • Coool Article. but it does not explain if want to show string datatype other than int.

  • This is really good. Good work.

  • Great work, really thank you

  • Small Query Work Great Thanks

  • Thanks for the great article, I have only one comment.

    If the cursor returns 0 rows in this case @AllSubjects will be an empty string, which will result in an error in the SubjectQuery Syntax.

    Possible fix:
    Add a simple if condition, after the first Cursor Fetch:

    if(@@FETCH_STATUS0)
    RETURN
    while(@@FETCH_STATUS=0)

  • Yes there should realize the opportunity to RSS commentary, quite simply, CMS is another on the blog.

  • In your example you have used to sum the integer column value, if suppose the "Grade' column as varchar then what will happen ?
    Can you please post your answer ?

  • Can u please elaborate on how to do this for strings ?

    what I want is like this :

    select aID,sum(Bcode),RCode,ISCode,SEcode
    from(
    SELECT distinct(aID),
    (CASE areftyp WHEN 'Bcode' THEN NVL(arefnum, '') END) AS Bcode,
    (CASE areftyp WHEN 'RCode' THEN NVL(arefnum, '') END) AS RCode,
    (CASE areftyp WHEN 'ISCode' THEN NVL(arefnum, 0) END) As ISCode,
    (CASE areftyp WHEN 'SEcode' THEN NVL(arefnum, 0) END) AS SEcode
    FROM table_ref where aID=2) a
    ;

    where Bcode,RCode,ISCode and SEcode are strings...

    and result I am getting for the inner query is :

    aID Bcode RCode ISCode SEcode

    2 MAKD (null) (null) (null)

    2 (null) nmj (null) (null)

    2 (null) (null) jfh77 (null)

    2 (null) (null) (null) (null)

    2 (null) (null) (null) 76745

  • I solved it up..
    needs to add proper group by statements...

  • Can we get column Sum(Physics) i mean Physics SUM for all students as a row (like footer...)

  • well done but i want to ad another colume peside degre names degre1 how can apply that

  • how can you read:))

  • Thanks a lot for the article.

    But it gives me an error
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name

    What is the problem in general?

    -> Thanks in advance...

  • I want to do this things with string
    like
    ID PHY CHE MAT BIO
    78445 Y Y N N
    78448 Y N Y Y
    78449 Y N N N
    78441 Y N N Y
    78442 Y N Y N
    78746 Y Y N N

  • Why do I get a 'Incorrect syntax near keyword 'group' all the time???

    SELECT B.RR_Name,
    SUM(Bud) AS Act,
    SUM(Act) As Bud
    FROM

    (SELECT B.RR_Name,
    (CASE A.RS_Type WHEN 2 THEN ISNULL(A.RS_Total/1000,0) END) AS Bud,
    (CASE A.RS_Type WHEN 23 THEN ISNULL(A.RS_Total/1000,0) END) AS Act
    FROM RS A, RR B WITH(READPAST)
    WHERE A.RS_Code = B.RR_Rep AND A.RS_File = 'RR' AND A.RS_Type IN ('2','23') AND A.RS_Code IN ('d90', 'd57', 'd112', 'd138', 'd141') AND A.RS_Year > 20110000)
    Group By B.RR_Name

  • Gnarly article mate, keep that the excellent work, just shared this with ma friendz

  • Dark blue text on a black background? Seriously? Or is Chrome not loading your style sheets properly?

  • i have data like
    plan_id step_id date1 date2
    12 1 28-1-12 28-2-12
    12 2 29-1-12 28-2-12
    12 3 22-1-12 28-2-12
    12 4 23-1-12 28-2-12
    12 5 24-1-12 28-2-12

    i need this data like

    12 | 1 | 28-1-12 | 28-2-12 | 29-1-12 | 28-2-12 | 22-1-12 | 28-2-12 | 23-1-12 | 28-2-12 | 24-1-12 | 28-2-12


    please help me out




  • I cant read this, has very bad coloring.... Please... Please change the color of this blog.

  • perfect solution for rows to column.
    exactly what i was looking for!
    thanks a ton..!

  • hweIJO Thanks for sharing, this is a fantastic blog article.Thanks Again. Want more.

  • Major thanks for the post.

  • Unpecl Really appreciate you sharing this blog. Keep writing.

  • This is really nice POST
    Can you please help me out in a task given to me
    I want tot add/sum every three rows in my data
    Actually i have Marks sheet, Students marks are in rows
    three rows three subject marks then net student roll number start
    So i don't know how to add their marks
    I have a huge data,its a bit simpler on limited number of rows but now i don't know how to do this on rows which are above 6 lac
    Appreciate your reply

  • I don't leave a comment, but I browsed a few of the responses on Rows to Columns - Salim Fayad. I do have 2 questions for you if it's okay.
    Could it be only me or does it look as if like a few of these remarks come across as if they are left
    by brain dead people? :-P And, if you are writing on other online social
    sites, I would like to follow you. Would you post a list
    of every one of all your social community pages like your linkedin profile,
    Facebook page or twitter feed?

Comments have been disabled for this content.