Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
Practice
ss sqlpractice2014
SQL Problem Template
hdudh
BRYAN_BD
SQL Server NULL replacement with dynamic value
TUAN 8_BTTL
Shalvika's Query
service_delivery_task
PracticeDB