Row wise arithmetic operation using pivot and unpivt, Sql Server

Row wise arithmetic operation using pivot and unpivt

CREATE TABLE Sales (EmpId INT, Yr INT, price MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)

select * from Sales

select *,sum(totalsales) over() as 'Grand Total' from
        (
        select *,sum(totalsales) over(partition by EmpId) as 'total' from 
            (SELECT EmpId, Yr, SUM(price) as totalsales FROM Sales GROUP BY EmpId, Yr)
        as abc
        )
    as def

go 
declare @yearColumn varchar(500)
declare @yearColumnCommaSeperated varchar(500)
set @yearColumn = ''
set @yearColumnCommaSeperated = ''

select @yearColumnCommaSeperated = @yearColumn+'['+CONVERT(varchar(5), Yr)+'], ' 
	 from Sales
	 where yr is not null

set @yearColumn= STUFF((SELECT distinct ',' + QUOTENAME(yr) 
            FROM Sales c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
declare @totalColumn varchar(500)        
        set @totalColumn= STUFF((SELECT distinct '+' + QUOTENAME(yr) 
            FROM Sales c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @yearColumn

declare @query varchar(500) 
set @query = '
select *
from 
(
  select *,sum(totalsales) over(partition by EmpId) as total from (
SELECT EmpId, Yr, SUM(price) as totalsales
FROM Sales
GROUP BY EmpId, Yr ) as abc
) src pivot
(
  sum(totalsales)
  for yr in ('+@yearColumn+')
) piv  order by piv.EmpID'

exec(@query)


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development