Dynamic PIVOT with WHERE condition in SQL Server 2005

I have written many articles on PIVOT even I feel to write one more on practical situation. Suppose you have one table which contain studentID, year of student, total marks, increase and decrease. We need to find list of student on increase and decrease. How can we do that?

Have a look at this example with dummy data.

CREATE TABLE StudDetail

(

StudentID INT,

PassYear VARCHAR(10),

Grades int,

Increase INT,

Decrease INT

)

GO

INSERT INTO StudDetail

SELECT 1, ’08-09′, 3333,0,0 union all

SELECT 1, ’09-10′, 4252,25,0 union all

SELECT 2, ’08-09′, 2100,0,0 union all

SELECT 2, ’09-10′, 2002,0,-10

GO

–pivot query

SELECT * from (select StudentID,PassYear,grades FROM StudDetail where increase>=0) up

PIVOT (sum(grades) for passyear in ([08-09], [09-10])) AS pivo

–I can work with above query but what if I don’t know how much year I have to make column???

–I have to go for dynamic query, let us create it.

–START creating dynamic code for PIVOT

DECLARE @Cols NVARCHAR(2000)

SET @Cols=

–make column list for PIVOT

SELECT @Cols=@Cols+ ‘[‘+s.Passyear +‘]’+ ‘, ‘ FROM

(SELECT DISTINCT PassYear FROM StudDetail) AS s

–remove last comma from column list

SET @Cols=LEFT(@Cols,LEN(@Cols)-1)

–create pivot query as we have just added distinct year list in @Cols variable

SET @Cols=‘SELECT * from (select StudentID,PassYear,grades FROM StudDetail where increase>=0) up

PIVOT (sum(grades) for passyear in (‘+@cols+‘)) AS pivo’

–print query and check

print @cols

EXECUTE sp_executeSQL @Cols

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Advertisements

8 Responses to “Dynamic PIVOT with WHERE condition in SQL Server 2005”

  1. Anonymous Says:

    I got work but if I add filters in inside of set such as :[MEthod]= 'SHIPPING_DATE'..the symbol " ' "give an error : Invalid column name 'Shipping_Date'

  2. Anonymous Says:

    I got work but if I add filters in inside of set such as :[MEthod]= 'SHIPPING_DATE'..the symbol " ' "give an error : Invalid column name 'Shipping_Date'

  3. Ritesh Shah Says:

    can you please show me your full script if it is possible?

  4. Ritesh Shah Says:

    can you please show me your full script if it is possible?

  5. Anonymous Says:

    — using two time single quote allow to use [Method] = ''Ship_Date'' 😀 make work it is workgin

  6. Anonymous Says:

    — using two time single quote allow to use [Method] = ''Ship_Date'' 😀 make work it is workgin

  7. Anonymous Says:

    what about to do a total sum of the pivot columns header ? with (sum(grades) over(partition by ID)) as total

  8. Anonymous Says:

    what about to do a total sum of the pivot columns header ? with (sum(grades) over(partition by ID)) as total


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: