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
https://www.hsbc.co.in/ways-to-bank/online-banking/9247/
Using pre-built schema in sql server
M0413045.sql
sqript for print !00 numbers with out loop
Rename column name using 'sp_rename' command
Delete cascade
Test Link samll Data
product_sales
Primeiro
Sum then delete one row