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
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
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
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)
–make column list for PIVOT
SELECT @Cols=@Cols+ ‘[‘+s.Passyear +‘]’+ ‘, ‘ FROM
(SELECT DISTINCT PassYear FROM StudDetail) AS s
–remove last comma from column list
–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
EXECUTE sp_executeSQL @Cols