PIVOT AND UNPIVOT USING XML PARSING , Sql Server

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

λ

                                      .NET NoSQL database for rapid development