Query to merge data

Question

Query to merge data

sqlinterset

Ten Centuries

Points: 1341
More actions
March 20, 2018 at 10:47 am

#329865

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'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

sqlinterset - Tuesday, March 20, 2018 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) 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/

sqlinterset Ten Centuries Points: 1341 More actions · Answer 2

LinksUp - Tuesday, March 20, 2018 11:28 AM
sqlinterset - Tuesday, March 20, 2018 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) 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

sgmunson SSC Guru Points: 110640 More actions · Answer 3

sqlinterset - Tuesday, March 20, 2018 12:58 PM
LinksUp - Tuesday, March 20, 2018 11:28 AM
sqlinterset - Tuesday, March 20, 2018 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) 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)