Mapping from multiple row items to single row

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