unpivot with two columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

unpivot with two columns

Author

Topic

Dev@nlkss

134 Posts

Posted - 2009-05-29 : 01:21:20

hi all,
can i use like the following way,actually its giving error, what is the alternate way for this.

-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5,Ven1,,Ven2,Ven3,Ven4,Ven5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
AND VendorID(Ven1,,Ven2,Ven3,Ven4,Ven5)
)AS unpvt

my requirement is my sp accepts Empno then i have to select corresponding vendorname,
assume i pass Emp2 then my output shold like
Emp--Vendor
emp2--Ven2
thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-29 : 14:16:57

[code]SELECT Emp,Ven
FROM
(
SELECT VendorID,Emp1 AS Emp,Ven1 AS Ven
FROM Table
UNION ALL
SELECT VendorID,Emp2,Ven2
FROM Table
UNION ALL
SELECT VendorID,Emp3,Ven3
FROM Table
UNION ALL
SELECT VendorID,Emp4 ,Ven4
FROM Table
UNION ALL
SELECT VendorID,Emp5,Ven5
FROM Table
)t
WHERE t.Emp=@Emp
[/code]
@Emp is passed emp value

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 14:42:29

And to preserve SQL Server resources,

SELECT VendorID, Emp1 AS Emp, Ven1 AS Ven FROM Table WHERE Emp1 = @Emp
UNION ALL
SELECT VendorID, Emp2, Ven2 FROM Table WHERE Emp2 = @Emp
UNION ALL
SELECT VendorID, Emp3, Ven3 FROM Table WHERE Emp3 = @Emp
UNION ALL
SELECT VendorID, Emp4, Ven4 FROM Table WHERE Emp4 = @Emp
UNION ALL
SELECT VendorID, Emp5, Ven5 FROM Table WHERE Emp5 = @Emp

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources