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.
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.
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.
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!
@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!
ASKER
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!
As much as you are aware at this point in time, of course!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
sbornstein2, a feedback will be appreciated.
ASKER
Sorry for delay thanks for the assistance.
Open in new window