Rows to Columns - Salim Fayad
Wednesday, January 30, 2008 7:36 PM The Eagle

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

 

Filed under:

Comments

# re: Rows to Columns

Wednesday, July 30, 2008 6:58 AM by Anish

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

Thanks

# re: Rows to Columns

Wednesday, October 8, 2008 6:14 AM by Ashwin

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

# re: Rows to Columns

Thursday, January 29, 2009 5:44 AM by reckface

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

# re: Rows to Columns

Monday, March 9, 2009 2:45 PM by Ashu

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

# re: Rows to Columns

Wednesday, March 25, 2009 1:10 AM by aloksinha83@gmail.com

The different approaches are excellent and beautifully explained.

# re: Rows to Columns

Monday, April 13, 2009 1:37 PM by The Eagle

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

# re: Rows to Columns

Sunday, May 17, 2009 3:16 AM by sheju

Nice work, It helped me a lot .

Thanks

# re: Rows to Columns

Tuesday, August 25, 2009 5:21 PM by Tsega

Very Nice Job !!! I was very helpfull

# re: Rows to Columns

Tuesday, April 20, 2010 9:10 AM by LOLOUDOUDARA

That was great. Thanks

# re: Rows to Columns

Thursday, July 1, 2010 7:59 AM by santhosh

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

# re: Rows to Columns

Wednesday, August 25, 2010 11:03 AM by Nuno Pereira

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

Great help!

Thanks!

# re: Rows to Columns

Monday, August 30, 2010 1:40 AM by Harish

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

# re: Rows to Columns

Wednesday, September 1, 2010 2:27 PM by Mohsin Khan

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

# re: Rows to Columns

Tuesday, September 14, 2010 9:59 AM by Pooja NK

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...

# re: Rows to Columns

Tuesday, September 21, 2010 11:39 AM by Sys

Just perfect. Thanks a lot!!!

# re: Rows to Columns

Wednesday, November 17, 2010 7:58 AM by kishore

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

# re: Rows to Columns

Tuesday, December 7, 2010 11:26 AM by abhi

great job buddy. Helped me a lot...

# re: Rows to Columns

Saturday, January 8, 2011 3:57 PM by ipad stand

-----------------------------------------------------------

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

# re: Rows to Columns

Thursday, February 3, 2011 3:57 AM by Tehmina

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

# re: Rows to Columns

Friday, April 22, 2011 3:42 PM by Tariq

This is really good. Good work.

# re: Rows to Columns

Wednesday, May 18, 2011 7:02 AM by Moataz

Great work, really thank you

# re: Rows to Columns

Wednesday, June 1, 2011 6:31 AM by Anonymous

Small Query Work Great Thanks

# re: Rows to Columns

Tuesday, July 26, 2011 7:55 AM by Rows to columns

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_STATUS<>0)

RETURN

while(@@FETCH_STATUS=0)

# re: Rows to Columns

Saturday, August 27, 2011 12:44 AM by tryecrot

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

# re: Rows to Columns

Thursday, September 15, 2011 5:51 AM by Suresh

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 ?

# re: Rows to Columns

Tuesday, November 15, 2011 1:50 AM by mieow

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

# re: Rows to Columns

Tuesday, November 15, 2011 7:41 AM by mieow

I solved it up..

needs to add proper group by statements...

# re: Rows to Columns

Thursday, November 17, 2011 1:45 AM by Sandeep

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

# re: Rows to Columns

Tuesday, December 13, 2011 1:23 PM by hussein

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

# re: Rows to Columns

Wednesday, January 4, 2012 6:35 PM by crauzer

how can you read:))

# re: Rows to Columns

Tuesday, March 20, 2012 5:43 PM by Cool

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...

# re: Rows to Columns

Wednesday, March 21, 2012 2:25 AM by udham singh

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

# re: Rows to Columns

Tuesday, May 8, 2012 4:14 AM by Bruce

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

# re: Rows to Columns

Saturday, June 2, 2012 12:07 PM by Soliz

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

# re: Rows to Columns

Tuesday, June 12, 2012 6:03 PM by Marvo

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

# re: Rows to Columns

Sunday, June 17, 2012 2:16 AM by vikas

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

# re: Rows to Columns

Wednesday, July 4, 2012 6:23 AM by creativemanix

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

# re: Rows to Columns

Thursday, August 2, 2012 5:38 AM by av

perfect solution for rows to column.

exactly what i was looking for!

thanks a ton..!

# re: Rows to Columns

Saturday, October 20, 2012 3:26 AM by cheap seo services

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

# re: Rows to Columns

Sunday, November 25, 2012 8:56 AM by imagenes de amor

Major thanks for the post.

# re: Rows to Columns

Thursday, January 31, 2013 10:28 AM by pills for weight loss

Unpecl Really appreciate you sharing this blog. Keep writing.

# re: Rows to Columns

Saturday, February 2, 2013 9:24 AM by Mariyah

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

# re: Rows to Columns

Tuesday, April 16, 2013 4:38 AM by Derr

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?