Run Code
|
API
|
Code Wall
|
Users
|
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
Please
log in
to post a comment.
NOT NULL field from SELECT INTO
テスト2
Demo
PracticeDB
test22
kirthi
Insert multiple parent-child records
Microsoft SQL Server T-SQL in 10mn ~ Lesson 15. Creating Advanced Joins samples...
sql_fb
forming date as nvarchar
Please log in to post a comment.