Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
PIVOT AND UNPIVOT USING XML PARSING
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int); INSERT INTO pvt VALUES (1,4,3,5,4,4); INSERT INTO pvt VALUES (2,4,1,5,5,5); INSERT INTO pvt VALUES (3,4,3,5,4,4); INSERT INTO pvt VALUES (4,4,2,5,5,4); INSERT INTO pvt VALUES (5,5,1,5,5,5); select * from pvt --Unpivot the table. -- Temperary Table create table #temptable (VendorID int not null,Employee varchar(10) not null, Orders int not null ) insert into #temptable SELECT VendorID, Employee, Orders FROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p UNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) )AS unpvt; select * from #temptable -- PIVOT TABLE WITH XML PARSING DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); set @cols= STUFF((SELECT distinct ',' + QUOTENAME(c.Employee) FROM #temptable c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') select @cols set @query='select * from ( select VendorID, Employee, Orders from #temptable ) src pivot ( sum(Orders) for Employee in ('+@cols+' ) ) pvt' exec(@query)
run
|
edit
|
history
|
help
0
SqlServer1282018
Stuff code in sql
TEST 1
QUAN LY CHUYEN BAY
20181CSE0041
SQL Interview Questions : Customer Orders
Sql Server's curse
BRYAN_BD
New
Names