InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 10:58:18 GMT20http://www.sqlservercentral.com/Forums/Topic1355655-338-1.aspxYou are looking for someone to spoon-feed you a solution, without trying to examine exactly what UNIQUEnTuples does.[code="sql"]-- Solution 4: Generate all combinations;WITH UNIQUEnTuples (n, Class, Product, Tuples, Product2) AS ( SELECT 1, Class, Product ,CAST(Product AS VARCHAR(MAX)), NULL FROM @ProductClass UNION ALL SELECT 1 + n.n, t.Class, t.Product ,CAST(t.Product AS VARCHAR(MAX)) + '+' + n.Tuples ,n.Product FROM @ProductClass t JOIN UNIQUEnTuples n ON CAST(t.Product AS VARCHAR(MAX)) < n.Tuples AND n.Class <> t.Class WHERE CHARINDEX(CAST(t.Product AS VARCHAR(MAX)), n.Tuples) = 0 )SELECT *FROM UNIQUEnTuples[/code]Start with the above. I have eliminated the constraint on how many products are combined.You'll need to decompose the prior code I provided to you to figure out how to split the tuples and then combine them to suit your needs.Mon, 24 Sep 2012 02:49:02 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1355655-338-1.aspxHi 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 , 1000UNION ALL SELECT 100, 1 , 2000UNION ALL SELECT 100, 1 , 3000UNION ALL SELECT 100, 2 , 1000UNION ALL SELECT 100, 2 , 2000Insert into @TableB SELECT 200 , 1000UNION ALL SELECT 300 , 1000UNION ALL SELECT 400 , 1000UNION ALL SELECT 500 , 2000UNION ALL SELECT 600 , 2000UNION ALL SELECT 800 , 3000UNION ALL SELECT 900 , 3000My Output will be Result Set as below Group ID ||Item Group ID ( Auto Generated) ||Product ID1 1 2001 1 5001 1 8001 2 2001 2 6001 2 8001 3 2001 3 5001 3 9001 4 2001 4 6001 4 9001 5 3001 5 5001 5 8001 6 3001 6 6001 6 8001 7 3001 7 5001 7 9001 8 3001 8 6001 8 9001 9 4001 9 5001 9 8001 10 4001 10 6001 10 8001 11 4001 11 5001 11 9001 12 4001 12 6001 12 9002 1 2002 1 5002 2 2002 2 6002 3 3002 3 5002 4 3002 4 6002 5 4002 5 5002 6 4002 6 600 Mon, 24 Sep 2012 02:23:23 GMTavssrkhttp://www.sqlservercentral.com/Forums/Topic1355655-338-1.aspxI'll be honest that I don't really understand the way you've expressed your requirement, however I believe this solution matches the result set you listed.[code="sql"]-- Solution 3: List combinations up to 3;WITH UNIQUEnTuples (n, Class, Product, Tuples, Product2) AS ( SELECT 1, Class, Product ,CAST(Product AS VARCHAR(MAX)), NULL FROM @ProductClass UNION ALL SELECT 1 + n.n, t.Class, t.Product ,CAST(t.Product AS VARCHAR(MAX)) + '+' + n.Tuples ,n.Product FROM @ProductClass t JOIN UNIQUEnTuples n ON CAST(t.Product AS VARCHAR(MAX)) < n.Tuples AND n.Class <> t.Class WHERE CHARINDEX(CAST(t.Product AS VARCHAR(MAX)), n.Tuples) = 0 AND n < 3)SELECT [Group ID]=CASE n WHEN 3 THEN 1 ELSE 2 END, [Item Group ID]=[Group ID], Product=itemFROM ( SELECT [Group ID]=ROW_NUMBER() OVER (PARTITION BY n ORDER BY Product2, Product) ,[Product Group]=Tuples, n FROM UNIQUEnTuples WHERE n IN (2,3)) aCROSS APPLY dbo.DelimitedSplit8K([Product Group], '+')ORDER BY CASE n WHEN 3 THEN 1 ELSE 2 END[/code]Sun, 23 Sep 2012 19:02:58 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1355655-338-1.aspxMy Initial Data Sample Still valid ...but I mentioned the all possible Combinations in my latest postDECLARE @ClassID TABLE (Class INT, Product INT)DECLARE @ProductClass TABLE(Product INT, Class INT)---@ProductClass table Product is pk INSERT INTO @ClassIDSELECT 1000, 100UNION ALL SELECT 2000, 100UNION ALL SELECT 3000, 100UNION ALL SELECT 1000, 100UNION ALL SELECT 2000, 100INSERT INTO @ProductClassSELECT 200, 1000UNION ALL SELECT 300, 1000UNION ALL SELECT 400, 1000UNION ALL SELECT 500, 2000UNION ALL SELECT 600, 2000UNION ALL SELECT 800, 3000UNION ALL SELECT 900, 3000Result Set as below Group ID ||Item Group ID ( Auto Generated) ||Product ID1 1 2001 1 5001 1 8001 2 2001 2 6001 2 8001 3 2001 3 5001 3 9001 4 2001 4 6001 4 9001 5 3001 5 5001 5 8001 6 3001 6 6001 6 8001 7 3001 7 5001 7 9001 8 3001 8 6001 8 9001 9 4001 9 5001 9 8001 10 4001 10 6001 10 8001 11 4001 11 5001 11 9001 12 4001 12 6001 12 9002 1 2002 1 5002 2 2002 2 6002 3 3002 3 5002 4 3002 4 6002 5 4002 5 5002 6 4002 6 600Thu, 20 Sep 2012 23:51:51 GMTavssrkhttp://www.sqlservercentral.com/Forums/Topic1355655-338-1.aspx[quote][b]avssrk (9/20/2012)[/b][hr]In Table A , Any number of Classes can be Grouped to a Group In Table A , Same set of Classes Cannot be Grouped In Table B I would like say the any Number of Products can grouped a class ( min 1) * if my Table A Product id is part of Configured Classes That Rule Set ( Item Group in Tablec not be Generated )Furhter ,I will Copy the Generated matrix to simaler result Set table. When user changes in existing configration and trigger the Regeneration . My the old item Group Id should be retained (based on old configration ) and increamental group to be generated.I have attached the source and and output.. here [/quote]I would be happy to try help you but you'll need to set up your new source data in the following format because I see that it has changed.[code="sql"]DECLARE @ClassID TABLE (Class INT, ID INT)DECLARE @ProductClass TABLE(Product INT, Class INT)INSERT INTO @ClassIDSELECT 1654, 1UNION ALL SELECT 1655, 1INSERT INTO @ProductClassSELECT 538, 1654UNION ALL SELECT 539, 1654UNION ALL SELECT 540, 1655UNION ALL SELECT 541, 1655[/code]I'll check back this weekend and see if you've done that then try to understand what the currently stated requirements are saying.Thu, 20 Sep 2012 23:08:22 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1355655-338-1.aspxIn Table A , Any number of Classes can be Grouped to a Group In Table A , Same set of Classes Cannot be Grouped In Table B I would like say the any Number of Products can grouped a class ( min 1) * if my Table A Product id is part of Configured Classes That Rule Set ( Item Group in Tablec not be Generated )Furhter ,I will Copy the Generated matrix to simaler result Set table. When user changes in existing configration and trigger the Regeneration . My the old item Group Id should be retained (based on old configration ) and increamental group to be generated.I have attached the source and and output.. here Thu, 20 Sep 2012 07:24:26 GMTavssrkhttp://www.sqlservercentral.com/Forums/Topic1355655-338-1.aspxThanks very much. Its impressive work..Sat, 08 Sep 2012 23:54:19 GMTavssrkhttp://www.sqlservercentral.com/Forums/Topic1355655-338-1.aspxInteresting problem. Fortunately I developed a script "UNIQUEnTuples" in this article that will help:[url]http://www.sqlservercentral.com/articles/sql+n-Tuples/89809/[/url]First, while you did do a good job of showing your expected results, you should always post DDL and set up data in a readily consumable form, thusly:[code="sql"]DECLARE @ClassID TABLE (Class INT, ID INT)DECLARE @ProductClass TABLE(Product INT, Class INT)INSERT INTO @ClassIDSELECT 1654, 1UNION ALL SELECT 1655, 1INSERT INTO @ProductClassSELECT 538, 1654UNION ALL SELECT 539, 1654UNION ALL SELECT 540, 1655UNION ALL SELECT 541, 1655[/code]The article will help you understand how the following solutions work:[code="sql"]-- Solution 1: Combine the products ;WITH UNIQUEnTuples (n, Class, Product, Tuples, Product2) AS ( SELECT 1, Class, Product ,CAST(Product AS VARCHAR(MAX)), NULL FROM @ProductClass UNION ALL SELECT 1 + n.n, t.Class, t.Product ,CAST(t.Product AS VARCHAR(MAX)) + '+' + n.Tuples ,n.Product FROM @ProductClass t JOIN UNIQUEnTuples n ON CAST(t.Product AS VARCHAR(MAX)) < n.Tuples AND n.Class <> t.Class WHERE CHARINDEX(CAST(t.Product AS VARCHAR(MAX)), n.Tuples) = 0 AND n < 2)SELECT [Group ID]=ROW_NUMBER() OVER (ORDER BY Product2, Product) ,[Product Group]=TuplesFROM UNIQUEnTuplesWHERE n=2[/code][code="sql"]-- Solution 2: Now split out the results of the above by group;WITH UNIQUEnTuples (n, Class, Product, Tuples, Product2) AS ( SELECT 1, Class, Product ,CAST(Product AS VARCHAR(MAX)), NULL FROM @ProductClass UNION ALL SELECT 1 + n.n, t.Class, t.Product ,CAST(t.Product AS VARCHAR(MAX)) + '+' + n.Tuples ,n.Product FROM @ProductClass t JOIN UNIQUEnTuples n ON CAST(t.Product AS VARCHAR(MAX)) < n.Tuples AND n.Class <> t.Class WHERE CHARINDEX(CAST(t.Product AS VARCHAR(MAX)), n.Tuples) = 0 AND n < 2)SELECT Product=item, [Group ID]FROM ( SELECT [Group ID]=ROW_NUMBER() OVER (ORDER BY Product2, Product) ,[Product Group]=Tuples FROM UNIQUEnTuples WHERE n=2) aCROSS APPLY dbo.DelimitedSplit8K([Product Group], '+')[/code]The DelimitedSplit8K FUNCTION called in the last line is the community string splitter described by Jeff Moden in his article here: [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]Thu, 06 Sep 2012 19:40:24 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1355655-338-1.aspxI have Table A , and table B . table A holds the about the class of a product and table B holds the Product and class relationship . I need to generate a 2 result sets from these 2 tables . one is grouping the two products combinations from different product classes. and also . the group should also have separate child items ( products ) related in result set 2 . any idea what the method can use for this . ??? Collapse | Copy CodeTable A Class | id1654 | 11655 | 1 Collapse | Copy CodeTable B product | Class538 | 1654539 | 1654540 | 1655541 | 16552 Result Sets Required from above: Collapse | Copy CodeResults set 1 : Group id | Product group1 538+5402 | 539+5403 | 538+5414 | 539+541 Collapse | Copy Coderesult set 2 : product | Group id ( rank or some thing)538 | 1540 | 1538 | 2541 | 2539 | 3540 | 3539 | 4541 | 4Thu, 06 Sep 2012 16:16:12 GMTavssrk