Mapping from multiple items to single item

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

SQL Server 2012 Forums

Transact-SQL (2012)

Mapping from multiple items to single item

Author

Topic

vivekkam
Starting Member

5 Posts

Posted - 2013-09-26 : 17:23:42

1st Table is the Order Table which has old items.
2nd Table is Mapping Table which has mapping for each unique combinations.
3rd Table is the desired output.

Please help.

Thanks in advance.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-09-26 : 19:46:05

[CODE]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 @orders
values
(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)

insert into @mapping
values
('A', 'B', 'C', 'X', 'S'),
('D', '', '', 'Z', ''),
('E', 'B', 'C', 'Y', ''),
('Q', '', '', 'M', ''),
('J', 'B', 'C', 'N', '')

--/**/select * from @orders
--/**/select * from @mapping

select
a.OrderNo,
a.NewItem,
a.Quantity,
a.Price
from (
select
o.OrderNo,
m.Output1 NewItem,
o.Quantity,
o.Price
from
@orders o
inner join
@mapping m
on (o.OrderItem = m.Item1 or
o.OrderItem = m.Item2 or
o.OrderItem = m.Item3)
and o.Price <> 0

union all

select
o.OrderNo,
m.Output2,
o.Quantity,
o.Price
from
@orders o
inner join
@mapping m
on (o.OrderItem = m.Item1 or
o.OrderItem = m.Item2 or
o.OrderItem = m.Item3)
and o.Price <> 0
and m.Output2 <> ''
) a
order by
a.OrderNo,
a.Quantity DESC[/CODE]

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-28 : 05:23:51

[code]
declare @Order table
(
OrderNo int,
OrderItem char(1),
Qty int,
Price int
)

insert @Order
select 1,'A',3,960 union all
select 1,'B',1,0 union all
select 1,'C',1,0 union all
select 1,'D',2,200 union all
select 2,'E',5,100 union all
select 2,'B',1,0 union all
select 2,'C',1,0 union all
select 3,'Q',6,1000

declare @Mapping table
(
item1 char(1),
item2 char(1),
item3 char(1),
Output1 char(1),
Output2 char(1)
)

insert @Mapping
select 'A','B','C','X','S' union all
select 'D','','','Z','' union all
select 'E','B','C','Y','' union all
select 'Q','','','M','' union all
select 'J','B','C','N',''

SELECT o.OrderNo,p.v AS NewItem,o.Qty,o.Price
FROM @Order o
INNER JOIN (SELECT t.*
FROM @Mapping m
CROSS APPLY (VALUES(Item1,Output1),(Item1,Output2))t(u,v)
WHERE v > ''
)p
ON p.u = o.OrderItem

output
--------------------------------
OrderNo v Qty Price
--------------------------------
1 X 3 960
1 S 3 960
1 Z 2 200
2 Y 5 100
3 M 6 1000

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vivekkam
Starting Member

5 Posts

Posted - 2013-09-28 : 18:27:16

Hi Bustaz,

First of all thanks for your reply.

Your reply satisfies the current scenario but in case in the mapping table we have a mapped value for the combination of A and B as P and Q and for A alone as O then the output for Order No 1 will also include P,Q and O. But what I expect to see in the output is the same as shown in the figure.

Example if in an order all A,B and C were present it should take the mapped value for that, in case only A and B were present it should take the mapped value for that and in case only A then it should take the mapped value for that.

Below is your query with the change in the mapping table:

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 @orders
values
(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)

insert into @mapping
values
('A', 'B', 'C', 'X', 'S'),
('A', 'B', '', 'P', 'Q'),
('A', '', '', 'O', ''),
('D', '', '', 'Z', ''),
('E', 'B', 'C', 'Y', ''),
('Q', '', '', 'M', ''),
('J', 'B', 'C', 'N', '')

--/**/select * from @orders
--/**/select * from @mapping

select
a.OrderNo,
a.NewItem,
a.Quantity,
a.Price
from (
select
o.OrderNo,
m.Output1 NewItem,
o.Quantity,
o.Price
from
@orders o
inner join
@mapping m
on (o.OrderItem = m.Item1 or
o.OrderItem = m.Item2 or
o.OrderItem = m.Item3)
and o.Price <> 0

union all

select
o.OrderNo,
m.Output2,
o.Quantity,
o.Price
from
@orders o
inner join
@mapping m
on (o.OrderItem = m.Item1 or
o.OrderItem = m.Item2 or
o.OrderItem = m.Item3)
and o.Price <> 0
and m.Output2 <> ''
) a
order by
a.OrderNo,
a.Quantity DESC

The output here expected is same as shown in the picture because Order 1 had the combination of A,B and C and not A and B or A alone.

Waiting for your reply.

Thanks in advance.

vivekkam
Starting Member

5 Posts

Posted - 2013-09-28 : 18:30:19

Hi Visakh,

Thanks for taking time and replying to my post. I really appreciate that.

Your reply also satisfies the current scenario but in case in the mapping table we have a mapped value for the combination of A and B as P and Q and for A alone as O then the output for Order No 1 will also include P,Q and O. But what I expect to see in the output is the same as shown in the figure.

Example if in an order all A,B and C were present it should take the mapped value for that, in case only A and B were present it should take the mapped value for that and in case only A then it should take the mapped value for that.

Below is your query with the change in the mapping table:

declare @Mapping table
(
item1 char(1),
item2 char(1),
item3 char(1),
Output1 char(1),
Output2 char(1)
)

insert @Mapping
select 'A','B','C','X','S' union all
select 'A','B','','P','Q' union all
select 'A','','','O','' union all
select 'D','','','Z','' union all
select 'E','B','C','Y','' union all
select 'Q','','','M','' union all
select 'J','B','C','N',''

SELECT o.OrderNo,p.v AS NewItem,o.Qty,o.Price
FROM @Order o
INNER JOIN (SELECT t.*
FROM @Mapping m
CROSS APPLY (VALUES(Item1,Output1),(Item1,Output2))t(u,v)
WHERE v > ''
)p
ON p.u = o.OrderItem

The output here expected is same as shown in the picture because Order 1 had the combination of A,B and C and not A and B or A alone.

Waiting for your reply.

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources