CODEWITHSUNDEEP
sqlsql-serverpivot
Tone
Tone

Reputation: 783

pivot table and percentage comparison between columns

I'm new to using pivot tables with sql and have been stuck on this particular issue that I'm hoping someone can help. My script returns a list of assignments and counts the assignments for each year. I would like to add a column that has the percentage increase or decrease change from the previous year. 

SELECT *
FROM
(
SELECT [year_time], [assignment_code], [assignment_desc]
FROM raw_teacher
) AS source
PIVOT
(
    COUNT(assignment_code)
    FOR [year_time] IN ([2012], [2011], [2010], [2009], [2008])
) as pvt

Current Output:

enter image description here

Desired output... enter image description here

Upvotes: 0

Views: 1467

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can just do the calculation directly:

with cte as (
     SELECT *
     FROM
     (
     SELECT [year_time], [assignment_code], [assignment_desc]
     FROM raw_teacher
     ) AS source
     PIVOT
     (
         COUNT(assignment_code)
         FOR [year_time] IN ([2012], [2011], [2010], [2009], [2008])
     ) as pvt
    )
select assignment_desc, [2012], [2012]/[2011], [2011], [2011]/[2010],
                        [2010], [2010]/[2009], [2009], [2009]/[2008], [2008]
from cte

If the values can be 0, then you'll want to check for that:

select asignment_desc,
       [2012], (case when [2011] <> 0 then [2012]/[2011] end),
       [2011], (case when [2010] <> 0 then [2011]/[2010] end),
       . . .
from cte

Upvotes: 1

Related Questions