Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql server Data Matrix.


sql server Data Matrix.

Author
Message
avssrk
avssrk
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4867 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
avssrk
avssrk
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
Thanks very much. Its impressive work..
avssrk
avssrk
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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
Attachments
source.PNG (7 views, 11.00 KB)
op.png (8 views, 38.00 KB)
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4867 Visits: 6431
avssrk (9/20/2012)
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



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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
avssrk
avssrk
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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
1 1 200
1 1 500
1 1 800
1 2 200
1 2 600
1 2 800
1 3 200
1 3 500
1 3 900
1 4 200
1 4 600
1 4 900
1 5 300
1 5 500
1 5 800
1 6 300
1 6 600
1 6 800
1 7 300
1 7 500
1 7 900
1 8 300
1 8 600
1 8 900
1 9 400
1 9 500
1 9 800
1 10 400
1 10 600
1 10 800
1 11 400
1 11 500
1 11 900
1 12 400
1 12 600
1 12 900
2 1 200
2 1 500
2 2 200
2 2 600
2 3 300
2 3 500
2 4 300
2 4 600
2 5 400
2 5 500
2 6 400
2 6 600
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4867 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
avssrk
avssrk
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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... Smile

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
1 1 200
1 1 500
1 1 800
1 2 200
1 2 600
1 2 800
1 3 200
1 3 500
1 3 900
1 4 200
1 4 600
1 4 900
1 5 300
1 5 500
1 5 800
1 6 300
1 6 600
1 6 800
1 7 300
1 7 500
1 7 900
1 8 300
1 8 600
1 8 900
1 9 400
1 9 500
1 9 800
1 10 400
1 10 600
1 10 800
1 11 400
1 11 500
1 11 900
1 12 400
1 12 600
1 12 900
2 1 200
2 1 500
2 2 200
2 2 600
2 3 300
2 3 500
2 4 300
2 4 600
2 5 400
2 5 500
2 6 400
2 6 600
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4867 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search