Mapping from multiple row items to single row

Create Table Orders ([Order No] INT, OrdItem CHAR(1), Quantity int, Price int) 
INSERT INTO Orders VALUES(1,'A',2,260),(1,'B',1,0),(1,'C',1,150),(2,'A',3,960),(2,'B',1,0)
 
Create table Lookups (OrdItem1 CHAR(1),OrdItem2 CHAR(1),[Output] CHAR(1))
INSERT INTO Lookups VALUES('A','B','D')
SELECT [Order No],ISNULL(Output, o.OrdItem) AS OrdItem,MAX(Quantity) AS Quantity,MAX(Price) AS Price
FROM   Orders o
       LEFT JOIN (SELECT [Output],OrdItem
                  FROM   Lookups
                         CROSS APPLY ( VALUES (OrdItem1), (OrdItem2)) d(OrdItem)) t
              ON o.OrdItem = t.OrdItem
GROUP  BY [Order No],ISNULL(Output, o.OrdItem) 
ORDER  BY [Order No],OrdItem DESC 
 
 Drop table Lookups
  Drop table Orders