Try this,
DECLARE @ORDER TABLE([Order No] INT, OrdItem CHAR(1))
INSERT INTO @ORDER VALUES(1,'A')
INSERT INTO @ORDER VALUES(1,'B')
INSERT INTO @ORDER VALUES(1,'C')
INSERT INTO @ORDER VALUES(2,'A')
INSERT INTO @ORDER VALUES(2,'B')
-------------------------
DECLARE @REFERENCE TABLE(OrdItem1 CHAR(1),OrdItem2 CHAR(1),[Output] CHAR(1))
INSERT INTO @REFERENCE VALUES('A','B','D')
------------------------
SELECT DISTINCT [Order No], OrderItem_ FROM (
SELECT A.[Order No],CASE WHEN B.Output IS NULL THEN A.OrdItem ELSE B.Output END OrderItem_ FROM @ORDER A LEFT JOIN (
SELECT * FROM @REFERENCE
UNPIVOT
(OItem FOR OrdItem IN (OrdItem1,OrdItem2)) UPVT
) B ON A.OrdItem = B.OItem
) X
Regards, RSingh
No comments:
Post a Comment