Hi Dwain ,

Thanks for the Reply . I am Looking a Generic Solution for issue (not upto combinations up to 3).

If you Look the Attachments in my previous post . the Source.jpeg Attachment give you source Tables structure

And op.jpeg gives you the Expected output .

The Data Behaviour of in Source Tables as below

1. In Table A , Any number of Classes can be Grouped to a Group ( Group ID)

2. In Table A , Same set of Classes Cannot be Grouped Again

3. In Table B, any Number of Products can grouped a class ( min 1)

Logic is I can derive my child products from based on parent product and class association ( RGB product can be derived to red,bule ,green) . the Previous One you posted is failing for the Second group id combination.

I am OK to use the Loops etc to generate pattern... 🙂

Just re framed the SQLS as per the Screenshots.

DECLARE @TableA TABLE (Source_Product_id INT, Group_id int , Class_id int )

DECLARE @TableB TABLE (Class_id int, target_Product_id int )

Insert into @TableA

SELECT 100, 1 , 1000

UNION ALL SELECT 100, 1 , 2000

UNION ALL SELECT 100, 1 , 3000

UNION ALL SELECT 100, 2 , 1000

UNION ALL SELECT 100, 2 , 2000

Insert into @TableB

SELECT 200 , 1000

UNION ALL SELECT 300 , 1000

UNION ALL SELECT 400 , 1000

UNION ALL SELECT 500 , 2000

UNION ALL SELECT 600 , 2000

UNION ALL SELECT 800 , 3000

UNION ALL SELECT 900 , 3000

My Output will be

Result Set as below

Group ID||Item Group ID ( Auto Generated)||Product ID

11200

11500

11800

12200

12600

12800

13200

13500

13900

14200

14600

14900

15300

15500

15800

16300

16600

16800

17300

17500

17900

18300

18600

18900

19400

19500

19800

110400

110600

110800

111400

111500

111900

112400

112600

112900

21200

21500

22200

22600

23300

23500

24300

24600

25400

25500

26400

26600