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
20181cse0012dbmsca2
viernes_ alumnos
manual app locks
exp7
new
Change
bc160400784
Crea, confirma y despliega tablas
Task_2_Final
QLCB_CSDL