Run Code
|
API
|
Code Wall
|
Users
|
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
Please
log in
to post a comment.
Campeonato2DLuizFelipe
Ejercicios de SQL (I)9
SQL Server NULL replacement with dynamic value
test22
FIGURA5.2
Teacher
Names
Teacher
Get all dates between two given dates
My notes
Please log in to post a comment.