We help IT Professionals succeed at work.
Get Started

SQL Query Grouping Question Record Inserts

sbornstein2 asked
Last Modified: 2021-04-21
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.
Watch Question
IT Engineer
Distinguished Expert 2017
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE