Similar to how data dictionaries can provide a unique key value pair and you can group like keys together and manipulate the value. I need a way to harmonize SQL data rows that exists as unique groups themselves and create new key groups.
Current Data
| Product | Operation | Machine | Center |
|---|---|---|---|
| Item 1 | 10 | 12 | 100 |
| Item 1 | 20 | 5 | 100 |
| Item 1 | 30 | 7 | 100 |
| Item 2 | 10 | 12 | 100 |
| Item 2 | 20 | 5 | 100 |
| Item 2 | 30 | 7 | 100 |
| Item 3 | 10 | 3 | 100 |
| Item 3 | 20 | 9 | 100 |
| Item 4 | 10 | 3 | 100 |
| Item 4 | 20 | 9 | 100 |
| Item 5 | 10 | 12 | 100 |
| Item 5 | 20 | 6 | 100 |
Desired Output
Table 1
| NewKey | Operation | Machine | Center |
|---|---|---|---|
| 1 | 10 | 12 | 100 |
| 1 | 20 | 5 | 100 |
| 1 | 30 | 7 | 100 |
| 2 | 10 | 3 | 100 |
| 2 | 20 | 9 | 100 |
| 3 | 10 | 12 | 100 |
| 3 | 20 | 6 | 100 |
Table 2
| NewKey | Product |
|---|---|
| 1 | Item 1 |
| 1 | Item 2 |
| 2 | Item 3 |
| 2 | Item 4 |
| 3 | Item 5 |
It's a no brainer if I was able to use a coding language, because I could just use a dictionary and be done with it, but I'm limited to using SQL.
I don't even know where to start with this kind of problem. The only thing I could think of doing would be to create a table on the fly and try adding the records as a group to see if it would insert, but I have no idea how I would do a check for that in SQL to see if that collection of rows already exists.
