SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Mapping from multiple items to single item
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vivekkam
Starting Member

USA
5 Posts

Posted - 09/26/2013 :  17:23:42  Show Profile  Reply with Quote


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
Flowing Fount of Yak Knowledge

USA
1693 Posts

Posted - 09/26/2013 :  19:46:05  Show Profile  Reply with Quote
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


=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/28/2013 :  05:23:51  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vivekkam
Starting Member

USA
5 Posts

Posted - 09/28/2013 :  18:27:16  Show Profile  Reply with Quote
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.
Go to Top of Page

vivekkam
Starting Member

USA
5 Posts

Posted - 09/28/2013 :  18:30:19  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000