compile sql server online

Language: Layout:

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(partition by EmpId) as 'total' from (
SELECT EmpId, Yr, SUM(price) as totalsales
FROM Sales
GROUP BY EmpId, Yr) as abc

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'

exec(@query)

View schema
Execution time: 0.05 sec, rows selected: 30, rows affected: 7, absolute service time: 0.17 sec