Please find the below input table and the relationships

- A package is associated to a shipment and a shipment is put into a box.
- Group of packages also can be associated to a single shipment and each package can be put into different boxes.

Now if we look at the input table below at a shipment level

S1 is placed in B1 and B2.

S2 is placed in B2 and B3.

S3 is placed in B3 and B4.

and this can go on and at the same pattern, however it will get cut at a point.

S4 is placed into B4.

Now the above data I wanted to make to a group, so that I can move all the boxes together.

There could be a case where single package can go into single shipment and single box example in the case below of P10

Package Number | Shipment Number | Box Number |
---|---|---|

P1 | S1 | B1 |

P2 | S1 | B2 |

P3 | S2 | B2 |

P4 | S2 | B3 |

P5 | S3 | B3 |

P6 | S3 | B4 |

P7 | S4 | B4 |

P8 | S5 | B5 |

P9 | S5 | B6 |

P10 | S6 | B7 |

I would like to get two outputs

Output 1 : which has additional columns Group Number and Group Name

Package Number | Shipment Number | Box Number | Group Number | Group Name |
---|---|---|---|---|

P1 | S1 | B1 | 1 | G1 |

P2 | S1 | B2 | 1 | G1 |

P3 | S2 | B2 | 1 | G1 |

P4 | S2 | B3 | 1 | G1 |

P5 | S3 | B3 | 1 | G1 |

P6 | S3 | B4 | 1 | G1 |

P7 | S4 | B4 | 1 | G1 |

P8 | S5 | B5 | 2 | G2 |

P9 | S5 | B6 | 2 | G2 |

P10 | S6 | B7 | 3 | G3 |

Output 2 : which is grouped at a Box level and having Box linkage data

Box Number | Group Number | Group Name | Box Link |
---|---|---|---|

B1 | 1 | G1 | B2,B3,B4 |

B2 | 1 | G1 | B1,B3,B4 |

B3 | 1 | G1 | B1,B2,B4 |

B4 | 1 | G1 | B1,B2,B3 |

B5 | 2 | G2 | B6 |

B6 | 2 | G2 | B5 |

B7 | 3 | G3 |

I tried making self joins, product joins by creating two different tables at shipment level,box level somehow couldn't make it .