SQL Server Get All Combinations in Relation Table without Loop or Cursors

Question

I have two Tables, one called Variations Which contains Variations like colors, sizes, etc....,

And another Table Variation Attributes which has the values of these Variations.

An item can have n variations for example (Color and Size) or (Color, Size and Length).

I need to generate all possible combinations for Variation Values such as below:

Variations

Id  |Value
1   | Color
2   | Size
3   | Length

Variation Attributes

Id  | VariationId | Value
1   | 1           | Black
2   | 1           | Red
3   | 2           | Large
4   | 2           | Small
5   | 2           | Medium
6   | 3           | Tall

All Combinations

Id  | VariationId | VariationAttrubuteId 
1   | 1 (Color)   | 1 (Black)
1   | 2 (Size)    | 3 (Large)
1   | 3 (Length)  | 6 (Tall)
**
2   | 1 (Color)   | 2 (Red)
2   | 2 (Size)    | 4 (Large)
2   | 3 (Length)  | 6 (Tall)

** And So on

Is it possible to fill the All Combinations Table without a Loop.

Update: Added More info

Already Tried Inner join but, that will not return all combinations as described above. Also cross join will not work as it will return unrelated data. what I need is to find some way to group variations together.

Equation: D1 variations * D2 variations * D3 Variations * Dn Variations. Such that each combination is unique.

@Larnu Cross Join won't work here, see updated question, and please vote on reopening the question since it is not a duplicate. — Mozart, Nov 07 '19 at 06:36
@MozartAlKhateeb I can't understand this line 2 | 1 (Color) | 2 (Black) I think it's Red instead of Black — Amira Bedhiafi, Nov 07 '19 at 08:23

jean · Answer 1 · 2019-11-07T17:43:46.017

Just use CROSS JOIN.

create table Variations
(
    Id int not null,
    Value varchar(50) not null
)

create table Variation_Attributes
(
    Id int not null,
    VariationId int not null,
    Value varchar(50) not null
)
GO

insert into Variations
(Id, Value)
values
(1   , 'Color'),
(2   , 'Size'),
(3   , 'Length');

insert into Variation_Attributes
(Id  , VariationId , Value)
values
(1   , 1           , 'Black'),
(2   , 1           , 'Red'),
(3   , 2           , 'Large'),
(4   , 2           , 'Small'),
(5   , 2           , 'Medium'),
(6   , 3           , 'Tall');
GO


select *
 from Variations
 cross join Variation_Attributes

UPDATE

After OP edition we can better evaluate the question and the problem itself. This is a bad design problem leading to a complex solution. A better solution can be to redesign the tables. A table for each kind of property can work better here Color, Size Length.

On the other hand, if you must give attributes to an object, let's say it's a shop that sells electric devices for the kitchen, so you will need a relation table for each product and its possible attributes what "solves" the problem.

"Solves" is quoted here why it's possible the real problem is not the problem OP is trying to solve. A very common issue in the IT industry.

UPDATE 2

When someone calls the "It's legacy" card there's not much what you can do.

Of course, the solution is trivial for a fixed number of Variation.

select v0.Value, v1.Value, v2.Value
 from Variation_Attributes v0
 join Variation_Attributes v1 on v1.Id != v0.Id
 join Variation_Attributes v2 on v1.Id != v0.Id and v2.Id != v1.Id
where v0.VariationId = 1
and   v1.VariationId = 2
and   v2.VariationId = 3

it give us all six possibilities.

But for a dynamic scenario OP must use PIVOT or build the query dynamically. Example:

declare @index int = 0, @select varchar(max), @from varchar(max), @where varchar(max), @VariationId int;
declare MyLoop cursor fast_forward for (select Id from Variations);
open MyLoop;
fetch next from MyLoop into @VariationId
while @@FETCH_STATUS != -1
begin

    if (@index = 0)
    begin
        set @select = 'select v'+cast(@index as varchar)+'.Value as v'+cast(@index as varchar);
        set @from   = 'from Variation_Attributes v'+cast(@index as varchar);
        set @where  = 'where v'+cast(@index as varchar)+'.VariationId = '+cast(@VariationId as varchar);
    end
    else begin
        set @select = @select + ', v'+cast(@index as varchar)+'.Value as v'+cast(@index as varchar);
        set @from   = @from   + ' cross join Variation_Attributes v'+cast(@index as varchar);
        set @where  = @where  + ' and v'+cast(@index as varchar)+'.VariationId = '+cast(@VariationId as varchar);
    end

    set @index = @index + 1;

    fetch next from MyLoop into @VariationId;
end

--print @select;
--print @from;
--print @where;

close MyLoop;
deallocate MyLoop;

exec (@select+' '+@from+' '+@where);

For the example data it yelds

v0      v1      v2
------- ------- -------
Black   Large   Tall
Black   Small   Tall
Black   Medium  Tall
Red     Large   Tall
Red     Small   Tall
Red     Medium  Tall

This is not possible, these variations are dynamically created by the users and in addition to that the design is too old to be changed. — Mozart, Nov 07 '19 at 14:05
Exactly this is what I've done and already posted my answer. I only was trying to find a more neat way. to achieve same result. — Mozart, Nov 08 '19 at 09:50
@MozartAlKhateeb Yeah I wish I have more time to dig in it, Maybe I can use a CTE or PIVOT. There are minor tweaks in my answer, like not using temporary tables. To be sincere, depending on the real-world scenario I can put that kind of logic inthe BL layer, not in the DB. — jean, Nov 08 '19 at 10:36

score 0 · Answer 2 · answered Nov 06 '19 at 14:18

0

Getting all combinations in SQL is easy - all you need to do is a cross join:

SELECT Variations.Id, Variations.Value, VariationAttributes.Id, VariationAttributes.Value
FROM Variations
CROSS JOIN VariationAttributes

However, this will provide values that shouldn't exists like Size Black and Color Large. You probably want an inner join instead:

SELECT Variations.Id, Variations.Value, VariationAttributes.Id, VariationAttributes.Value
FROM Variations
INNER JOIN VariationAttributes
    ON Variations.Id = VariationAttributes.VariationId

answered Nov 06 '19 at 14:18

Zohar Peled

79,642
10
69
121

Right, but also inner join on it's own won't work, I need combination of all variations with attribute value only for that variation. – Mozart Nov 07 '19 at 06:21
That's why you should use an inner join, and not a cross join. Personally, I think this design is flawed to begin with - this is basically an EAV design which is considered by many as an anti-pattern. If you need a flexible schema, I would consider using either Json or XML as a property bag. If you don't need flexibility, I would suggest having a table for each variation - so you have a colors table where the rows are different color values, a size table where the rows are different sizes etc'. – Zohar Peled Nov 07 '19 at 06:31
Schema change is not an option now, and actually variations and attributes are dynamically created by users, I could have achieved the result with a cursor but, sill searching for a better way. "Would you mind voting on reopening the question ?" – Mozart Nov 07 '19 at 06:34
I've reopened since I don't think this is a duplicate of the question chosen, however still your question isn't very clear - Where does the extra Id column comes from in your desired results? – Zohar Peled Nov 07 '19 at 06:50
It can be any Id, but the point is to have unique to each set of dimensions. – Mozart Nov 07 '19 at 07:09

score 0 · Accepted Answer · answered Nov 07 '19 at 10:30

The only way possible I found is to use Dynamic SQL Combined with Cross Join and a loop.

DECLARE @sql NVARCHAR(MAX) = '',
        @where NVARCHAR(MAX) = '',
        @index INT = 0, @currentVariation INT = 0;

-- Store all variation id's for a specific item
SELECT sv.VariationId VariationId
INTO #vars
FROM StockVariations sv 
WHERE StockId = @someId
ORDER BY sv.VariationId ASC;

-- Loop through all variations
DECLARE curr Cursor FOR SELECT VariationId FROM #vars;
OPEN curr;
FETCH NEXT FROM curr INTO @currentVariation
WHILE @@Fetch_Status <> -1
BEGIN
    IF @index > 0
    BEGIN
        SET @sql = @sql + ' CROSS JOIN';
    END
    -- Append Each variation as a table to the Sql Script
    SET @sql = @sql + ' (SELECT * FROM VariationAttributes WHERE VariationId IN (SELECT VariationId FROM #vars)) tb' + CONVERT(NVARCHAR, @index);
    IF @where <> ''
    BEGIN 
        SET @where = @where + ' AND';
    END
    SET @where = @where + ' tb' + CONVERT(NVARCHAR, @index) + '.VariationId = ' + CONVERT(NVARCHAR, @currentVariation);
    SET @index = @index + 1;
    FETCH NEXT FROM curr INTO @currentVariation;
END

PRINT 'SELECT * FROM ' + @sql + ' WHERE ' + @where;
CLOSE curr;
DEALLOCATE curr;

EXEC ('SELECT * FROM ' + @sql + ' WHERE ' + @where);
DROP TABLE #vars

SQL Server Get All Combinations in Relation Table without Loop or Cursors

3 Answers3