Yureka Yureka - 9 days ago 6
SQL Question

Convert Result into Pivot with Total

Current table result :

year ponits Type pageview
2016 29 Process - Save 1
2016 29 Process - shown 46
2016 29 Process - step 1 19
2016 29 Process - step 2 15
2016 30 Process - Save 3
2016 30 Process - shown 26
2016 30 Process - step 1 14
2016 30 Process - step 2 12


And my expected output :

YEAR type 29 30 Total
2016 Process - Save 1 3 4
2016 Process - shown 46 26 72
2016 Process - step 1 19 14 33
2016 Process - step 2 15 12 27


DB - MS SQL Server.
last column 'Total' contains addition of 29 and 30.

Answer

Try This.

            select YEAR ,type,[29],[30],cast([29] as numeric)+cast([30] as numeric) As Total
             from
            #Table
            pivot
            (
            max([pageview])
                   FOR ponits IN ([29],[30])
            )as p