SQLTeam.com | Weblogs | Forums

Need help converting rows to collums and collums to rows

sql2012

#1

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
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)
GO

SELECT * FROM pvt

will give me a result
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5

my result needs to be something like

vendor_ID1 vendor_ID2 vendor_ID3 vendor_ID4 vendor_ID5

emp1 4 4 4 4 5
emp2 3 1 3 2 1
emp3 5 5 5 5 5
emp4 4 5 4 5 5
emp5 4 5 4 4 5

but when I try this

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
GO

I get this
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
3 Emp1 4
3 Emp2 3
3 Emp3 5
3 Emp4 4
3 Emp5 4
4 Emp1 4
4 Emp2 2
4 Emp3 5
4 Emp4 5
4 Emp5 4
5 Emp1 5
5 Emp2 1
5 Emp3 5
5 Emp4 5
5 Emp5 5

can someone help me with my query
Thanks in advance


#2

Can someone please help


#3

Try this:

select p.Employee
      ,p.[1] as vendor_ID1
      ,p.[2] as vendor_ID2
      ,p.[3] as vendor_ID3
      ,p.[4] as vendor_ID4
      ,p.[5] as vendor_ID5
  from (select Employee,VendorID,Orders
          from (select VendorID
                      ,Emp1
                      ,Emp2
                      ,Emp3
                      ,Emp4
                      ,Emp5
                  from pvt
               ) as p
         unpivot (Orders for Employee
              in  ([Emp1],[Emp2],[Emp3],[Emp4],[Emp5])
                 ) as u
       ) as u
 pivot (sum(Orders) for VendorId
    in  ([1],[2],[3],[4],[5])
       ) as p
;