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.