只需使用 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,1,'Black'),'Red'),2,'Large'),(4,'Small'),(5,'Medium'),(6,3,'Tall');
GO


select *
 from Variations
 cross join Variation_Attributes

更新

在OP版本之后，我们可以更好地评估问题和问题本身。 这是一个糟糕的设计问题，导致了复杂的解决方案。 更好的解决方案是重新设计表格。 在Color,Size Length处，每种属性的表格都可以更好地工作。

另一方面，如果您必须为对象赋予属性，则假设这是一家商店，该商店出售用于厨房的电子设备，因此您将需要每种产品及其可能属性的关系表，以“解决”问题

此处引用了“解决方案”，为什么真正的问题不是OP试图解决的问题。在IT行业中非常普遍的问题。

更新2

当有人打电话给“ It's legacy”卡时，您无能为力。

当然，对于固定数量的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

它给了我们全部六种可能性。

但是对于动态方案，OP必须使用PIVOT或动态构建查询。 示例：

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);

对于产生的示例数据

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

使用SQL轻松获取所有组合-您要做的就是交叉联接：

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

但是，这将提供不应该存在的值，例如“黑色大小”和“大颜色”。您可能需要内部联接：

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

我发现的唯一可能方法是将动态SQL与交叉连接和循环结合使用。

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