Hello all,
Looking for a clean way to do the following little tricky.
Table: Customer
CustomerDate CustomerName CustomerCode CustomerLocID Quantity
3/10/17 ABC Corp CDO 345XCC 100
3/10/17 ABC Corp CDO 345XCC 300
3/10/17 ABC Corp CDO 789UNN 120
3/10/17 ABC Corp CDO 789UNN 85
I have two tables I need to do the following:
Table: CustomerRoot
CustomerID CustomerDate CustomerName CustomerCode
(Identity Col)
Table: CustomerDetail
CustomerID CustomerLocID Quantity
I need to group the records above by the Customer fields record with just these fields:
CustomerDate CustomerName CustomerCode CustomerLocID
Gather those grouped sets then create one CustomerRoot record and two CustomerDetail records per CustomerLocID set. There is a reason I can't add the CustomerLocID to the CustomerRoot table some special circumstance I am dealing with these tables the CustomerRoot record won't be unique other than the Identity CustomerID. It is like I need to get the detail records together grouped then create the Root and Detail records. Example ResultSet:
Table: CustomerRoot - only want these fields in this root table
CustomerID CustomerDate CustomerName CustomerCode
1 3/10/17 ABC Corp CDO
2 3/10/17 ABC Corp CDO
Table: CustomerDetail
CustomerID CustomerLocID Quantity
1 345XCC 100
1 345XCC 300
2 789UNN 120
2 789UNN 85
Thanks for any help all.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.