March 20, 2018 at 10:47 am
I need to write a query to merge data.. basically all rows from one product (p1) should be assigned to another product (p2) is product is in group with other product. below is the underline data (input table) and product grouping (#productgrp table). output table (#output) shows desired format of data:
IF OBJECT_ID('TempDB..#input','U') IS NOT NULL
DROP TABLE #input
--===== Create the test table with
CREATE TABLE #input
(
Product varchar(10),
Rate Varchar(10)
)
insert into #input
(Product, Rate)
select 'P1','r1'
union all
select 'P1','r2'
union all
select 'P1','r3'
union all
select 'P2','r4'
union all
select 'P2','r5'
union all
select 'P2','r6'
union all
select 'P3','r7'
union all
select 'P3','r8'
union all
select 'P3','r9'
union all
select 'P3','r10'
--===== Create the test table with
CREATE TABLE #productgrp
(
Product varchar(10),
gp Varchar(10)
)
insert into #productgrp
(Product, gp)
select 'P1','G1'
union all
select 'P2','G1'
union all
select 'P1','G2'
union all
select 'P3','G2'
CREATE TABLE #output
(
Product varchar(10),
Rate Varchar(10)
)
insert into #output
(Product, Rate)
select 'P1','r1'
union all
select 'P1','r2'
union all
select 'P1','r3'
union all
select 'P1','r4'
union all
select 'P1','r5'
union all
select 'P1','r6'
union all
select 'P1','r7'
union all
select 'P1','r8'
union all
select 'P1','r9'
union all
select 'P1','r10'
union all
select 'P2','r1'
union all
select 'P2','r2'
union all
select 'P2','r3'
union all
select 'P2','r4'
union all
select 'P2','r5'
union all
select 'P2','r6'
union all
select 'P3','r1'
union all
select 'P3','r2'
union all
select 'P3','r3'
union all
select 'P3','r7'
union all
select 'P3','r8'
union all
select 'P3','r9'
union all
select 'P3','r10'
March 20, 2018 at 11:28 am
sqlinterset - Tuesday, March 20, 2018 10:47 AMI need to write a query to merge data.. basically all rows from one product (p1) should be assigned to another product (p2) without changing product id. Attached is the excel that has two tabs, first tab shows underlined data and second tab shows the required format. any help would be appreciated. Thanks.
I'm sorry, but I don't know you. So I'm not going to blindly download a file and hope that this "isn't the one". Please click on the link below the signature line on how to post DDL, data and expected results. I'm sure you will get a better response.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 20, 2018 at 12:58 pm
LinksUp - Tuesday, March 20, 2018 11:28 AMsqlinterset - Tuesday, March 20, 2018 10:47 AMI need to write a query to merge data.. basically all rows from one product (p1) should be assigned to another product (p2) without changing product id. Attached is the excel that has two tabs, first tab shows underlined data and second tab shows the required format. any help would be appreciated. Thanks.I'm sorry, but I don't know you. So I'm not going to blindly download a file and hope that this "isn't the one". Please click on the link below the signature line on how to post DDL, data and expected results. I'm sure you will get a better response.
Thanks. i did it
March 20, 2018 at 2:30 pm
sqlinterset - Tuesday, March 20, 2018 12:58 PMLinksUp - Tuesday, March 20, 2018 11:28 AMsqlinterset - Tuesday, March 20, 2018 10:47 AMI need to write a query to merge data.. basically all rows from one product (p1) should be assigned to another product (p2) without changing product id. Attached is the excel that has two tabs, first tab shows underlined data and second tab shows the required format. any help would be appreciated. Thanks.I'm sorry, but I don't know you. So I'm not going to blindly download a file and hope that this "isn't the one". Please click on the link below the signature line on how to post DDL, data and expected results. I'm sure you will get a better response.
Thanks. i did it
Okay, and now for the more challenging problem: How, exactly, do those two tables relate to each other? There's no group information present in the table that relates products to rates, and there's no table to relate rates to groups, so what, exactly, are the groups supposed to do? I ask because your expected results place P1 into both groups, but P2 into only G1, and P3 into only G2. I can't see the relationship from the data.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply