[SQL 2012] Output based on Mapping Table and Orders Table

---

Output based on Mapping Table and Orders Table Posted: 29 Sep 2013 03:26 AM PDT I have two tables,1st Table is the Order Table which has order no,old items, price and their quantity.2nd Table is Mapping Table which has mapping for each unique combinations of the old order items.3rd Table is the desired output.Some notes:1)Whenever in an order there is a combination of item1,item2,item3 of the mapping table it should retrieve output1 and output 2 for that, when in order there is a combination of item1 and item 2 of the mapping table it should retrieve output1 and output 2 for that, when in order there is only item 1 present it should retrieve output1 and output 2 for that.2)Also, price of item2 and item3 will always be 0. The output table should take the price and quantity of Item1 always.3) Its not necessary that Order No will be sequential. It can be like 456,789,989 etc.DDL and DML for my question:declare @orders table ( OrderNo int, OrderItem varchar(10), Quantity int, Price money )declare @mapping table ( Item1 varchar(10), Item2 varchar(10), Item3 varchar(10), Output1 varchar(10), Output2 varchar(10) )insert into @ordersvalues (1, 'A', 3, 960), (1, 'B', 1, 0), (1, 'C', 1, 0), (1, 'D', 2, 200), (2, 'E', 5, 100), (2, 'B', 1, 0), (2, 'C', 1, 0), (3, 'Q', 6, 1000), (4, 'B', 1, 0), (4, 'A', 3, 300), (5, 'A', 7, 4000)insert into @mappingvalues ('A', 'B', 'C', 'X', 'S'), ('A', 'B', '', 'P', 'R'), ('A', '', '', 'O', ''), ('D', '', '', 'Z', ''), ('E', 'B', 'C', 'Y', ''), ('Q', '', '', 'M', ''), ('J', 'B', 'C', 'N', '')-- Output Expecteddeclare @output table ( OrderNo int, NewItem varchar(10),Quantity int,Price money)insert into @outputvalues (1, 'X', 3, 960), (1, 'S', 3, 960), (1, 'Z', 2, 200), (2, 'Y', 5, 100), (3, 'M', 6, 1000), (4, 'P', 3, 300), (4, 'R', 3, 300), (5, 'O', 7, 4000)select * from @ordersselect * from @mappingselect * from @output-- Solution that I tried giving wrong outputDECLARE @OutputTable TABLE (orderNo int, newItem varchar(1), quantity int, price money);INSERT INTO @OutputTable(orderNo, newItem, quantity, price) SELECT o.orderNo, m.output1, o.quantity, o.price FROM @mapping as m INNER JOIN @orders as o ON m.item1 = O.orderItem AND o.price != 0 AND m.output1!=''INSERT INTO @OutputTable(orderNo, newItem, quantity, price) SELECT o.orderNo, m.output2, o.quantity, o.price FROM @mapping as m INNER JOIN @orders as o ON m.item1 = O.orderItem AND o.price != 0 AND m.output2!=''SELECT * FROM @OutputTable ORDER BY orderNoI have also attached the solution I tried from my end.Thanks in advance.[url=http://s10.This image host is not supported, please use another/93j6lzb3d/Query_Image.png][/url]

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610