Join two table and explode the data based on qty

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 2008 Forums

Transact-SQL (2008)

Join two table and explode the data based on qty

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-09-10 : 01:28:03

Hi Guys,

I have to join two tables and break it down based on the qty given in #TABLE1 and create an explode data from #table2 ang obtain the PO from #table1. I could not figure out on how to do this. I try to use left outer join but i got duplicate records. Please see below the sample data.

Your reply is very much appreaciate. Thanks.

[code]Create table #TABLE1
(SKU Nvarchar(35),
price numeric(28,12),
defectcode int,
PO nvarchar(35),
Qty int)
Insert into #table1(sku,price,defectcode,Po,Qty)values('P300-1710-DROID3-U',330.26,362,'11244078',3)
Insert into #table1(sku,price,defectcode,Po,Qty)values('P300-1710-DROID3-U',330.26,362,'11339866',1)

Create table #TABLE2
(SKU nvarchar(35),
ESN nvarchar(35),
defectcode int,
qty int)
Insert into #table2(SKU,ESN,Defectcode,Qty)values('P300-1710-DROID3-U','268435460406772043',362,1)
Insert into #table2(SKU,ESN,Defectcode,Qty)values('P300-1710-DROID3-U','268435460404715984',362,1)
Insert into #table2(SKU,ESN,Defectcode,Qty)values('P300-1710-DROID3-U','268435460404748629',362,1)
Insert into #table2(SKU,ESN,Defectcode,Qty)values('P300-1710-DROID3-U','268435460404921402',362,1)

RESULT
SKU------------------ESN----------------DefectCode---PO--------Qty
P300-1710-DROID3-U--268435460406772043----362--------11244078---1
P300-1710-DROID3-U--268435460404715984----362--------11244078---1
P300-1710-DROID3-U--268435460404748629----362--------11244078---1
P300-1710-DROID3-U--268435460404921402----362--------11339866---1
[\code]

Andy Hyslop
Starting Member

14 Posts

Posted - 2012-09-10 : 04:24:46

Why does 078 have a quantity of 1 in your results when it has a quantity of 3 in table 1?