Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql server Data Matrix. Expand / Collapse
Author
Message
Posted Thursday, September 06, 2012 4:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 4:03 AM
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
Post #1355655
Posted Thursday, September 06, 2012 7:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
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!
Post #1355692
Posted Saturday, September 08, 2012 11:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 4:03 AM
Points: 5, Visits: 25
Thanks very much. Its impressive work..
Post #1356449
Posted Thursday, September 20, 2012 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 4:03 AM
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



  Post Attachments 
source.PNG (4 views, 11.84 KB)
op.png (1 view, 38.64 KB)
Post #1361950
Posted Thursday, September 20, 2012 11:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
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!
Post #1362415
Posted Thursday, September 20, 2012 11:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 4:03 AM
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
Post #1362429
Posted Sunday, September 23, 2012 7:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
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!
Post #1363313
Posted Monday, September 24, 2012 2:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 4:03 AM
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... :)

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




Post #1363361
Posted Monday, September 24, 2012 2:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
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!
Post #1363370
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse