September 6, 2012 at 4:16 pm
I 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 Code
Table A
Class| id
1654| 1
1655| 1
Collapse | Copy Code
Table B
product| Class
538| 1654
539| 1654
540| 1655
541| 1655
2 Result Sets Required from above:
Collapse | Copy Code
Results set 1 :
Group id |Product group
1 538+540
2 | 539+540
3 | 538+541
4 | 539+541
Collapse | Copy Code
result set 2 :
product | Group id ( rank or some thing)
538 | 1
540 | 1
538 | 2
541 | 2
539 | 3
540 | 3
539 | 4
541 | 4
September 6, 2012 at 7:40 pm
Interesting problem. Fortunately I developed a script "UNIQUEnTuples" in this article that will help:
http://www.sqlservercentral.com/articles/sql+n-Tuples/89809/
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:
DECLARE @ClassID TABLE (Class INT, ID INT)
DECLARE @ProductClass TABLE(Product INT, Class INT)
INSERT INTO @ClassID
SELECT 1654, 1
UNION ALL SELECT 1655, 1
INSERT INTO @ProductClass
SELECT 538, 1654
UNION ALL SELECT 539, 1654
UNION ALL SELECT 540, 1655
UNION ALL SELECT 541, 1655
The article will help you understand how the following solutions work:
-- 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]=Tuples
FROM UNIQUEnTuples
WHERE n=2
-- 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) a
CROSS APPLY dbo.DelimitedSplit8K([Product Group], '+')
The DelimitedSplit8K FUNCTION called in the last line is the community string splitter described by Jeff Moden in his article here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 8, 2012 at 11:54 pm
Thanks very much. Its impressive work..
September 20, 2012 at 7:24 am
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
September 20, 2012 at 11:08 pm
avssrk (9/20/2012)
In Table A , Any number of Classes can be Grouped to a GroupIn 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
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.
DECLARE @ClassID TABLE (Class INT, ID INT)
DECLARE @ProductClass TABLE(Product INT, Class INT)
INSERT INTO @ClassID
SELECT 1654, 1
UNION ALL SELECT 1655, 1
INSERT INTO @ProductClass
SELECT 538, 1654
UNION ALL SELECT 539, 1654
UNION ALL SELECT 540, 1655
UNION ALL SELECT 541, 1655
I'll check back this weekend and see if you've done that then try to understand what the currently stated requirements are saying.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 20, 2012 at 11:51 pm
My Initial Data Sample Still valid ...but I mentioned the all possible Combinations in my latest post
DECLARE @ClassID TABLE (Class INT, Product INT)
DECLARE @ProductClass TABLE(Product INT, Class INT)
---@ProductClass table Product is pk
INSERT INTO @ClassID
SELECT 1000, 100
UNION ALL SELECT 2000, 100
UNION ALL SELECT 3000, 100
UNION ALL SELECT 1000, 100
UNION ALL SELECT 2000, 100
INSERT INTO @ProductClass
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
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
September 23, 2012 at 7:02 pm
I'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.
-- 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=item
FROM (
SELECT [Group ID]=ROW_NUMBER() OVER (PARTITION BY n ORDER BY Product2, Product)
,[Product Group]=Tuples, n
FROM UNIQUEnTuples
WHERE n IN (2,3)) a
CROSS APPLY dbo.DelimitedSplit8K([Product Group], '+')
ORDER BY CASE n WHEN 3 THEN 1 ELSE 2 END
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 24, 2012 at 2:23 am
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
September 24, 2012 at 2:49 am
You are looking for someone to spoon-feed you a solution, without trying to examine exactly what UNIQUEnTuples does.
-- 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
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply