Link to home
Create AccountLog in
Avatar of sbornstein2
sbornstein2

asked on

SQL Query Grouping Question Record Inserts

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.
Avatar of yo_bee
yo_bee
Flag of United States of America image

I think you can do this.

Select CustomerDate, CustomerName , CustomerCode , CustomerLocID  ,Quantity
from CustomerRoot inner join CustomerDetail on CustmerRoot.customerid = custmerdetail.customerid

Open in new window

Avatar of sbornstein2
sbornstein2

ASKER

No I need to know how to create the records in the Root and the detail records.
Is there a table called customers or you looking to create a table called customers  from custroot and custdetails ?

The way I read your data is that root and details has one column that is the unique id that joins the two relationships together.

I am sorry I do not understand your question.
@yo_bee - I think he wants to normalize the table. But lacks the key column, which he wants to generate and use it.

@sbornstein2, if I am right, it is not clear why you want CustomerDate in a master table? If you could clarify, please. It's okay, if you want it that way, but it would be nice to know the rationale!
You are correct.  I scaled this way down but essentially the root is like a rollup table to take unqiue fields and create a parent record then apply all the detail records using that identity generated parent id think of it.   This specific case works a little different so not as simple as creating a parent row in root then joining back to the data and creating the detail records.  The loc id throws this off.  There is rationale believe it or not :)
Is given source data (and shown expected output) all that is to it? Meaning, do we have enough to provide solution. Or there will be any twists in the tale (or in the data!)

As much as you are aware at this point in time, of course!
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
sbornstein2, a feedback will be appreciated.
Sorry for delay thanks for the assistance.