sql server Data Matrix.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » sql server Data Matrix.

sql server Data Matrix.

Rate Topic

Display Mode

Topic Options

Author

Message

avssrk

Posted Thursday, September 06, 2012 4:16 PM

Forum 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

dwain.c

Posted Thursday, September 06, 2012 7:40 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

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/

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1355692

avssrk

Posted Saturday, September 08, 2012 11:54 PM

Forum 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

avssrk

Posted Thursday, September 20, 2012 7:24 AM

Forum 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

dwain.c

Posted Thursday, September 20, 2012 11:08 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

avssrk (9/20/2012)

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.

Post #1362415

avssrk

Posted Thursday, September 20, 2012 11:51 PM

Forum 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

dwain.c

Posted Sunday, September 23, 2012 7:02 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

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

Post #1363313

avssrk

Posted Monday, September 24, 2012 2:23 AM

Forum 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

dwain.c

Posted Monday, September 24, 2012 2:49 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

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.

Post #1363370

« Prev Topic | Next Topic »

Permissions