make a combination (variations) of records on the basis of a dynamic column value (attributes) using sql

I have a table which contains Attributes

AttrId     Title  
1       |  Color  
2       |  Size  
3       |  Style

Another Table which contains Variants

  • varchar or nvarchar
  • Add column to table and then update it inside transaction
  • SQL Server 2014 - Multiple SUM/COUNT with subqueries
  • Why change in CommandTimeout not working
  • How do I get distinct characters of string column in mssql?
  • SSIS - The process cannot access the file because it is being used by another process
  • Id      |   ProductId   |    AttrId    |    Value  
    1       |   1           |    1         |    Red  
    2       |   1           |    1         |    Green  
    3       |   1           |    1         |    Blue  
    4       |   1           |    2         |    Small  
    5       |   1           |    2         |    Medium  
    6       |   1           |    2         |    Large  
    7       |   1           |    3         |    New  
    8       |   1           |    3         |    Used  
    

    Now i want to make all possible combination related to Attributes

    Output should be look like this:

    Size    |     Color     |    Style  
    
    Small   |     Red       |    New
    Small   |     Red       |    Old
    Small   |     Green     |    New 
    Small   |     Green     |    Old
    Small   |     Blue      |    New  
    Small   |     Blue      |    Old
    Medium  |     Red       |    New
    Medium  |     Red       |    Old
    Medium  |     Green     |    New 
    Medium  |     Green     |    Old
    Medium  |     Blue      |    New  
    Medium  |     Blue      |    Old
    Large   |     Red       |    New
    Large   |     Red       |    Old
    Large   |     Green     |    New 
    Large   |     Green     |    Old
    Large   |     Blue      |    New  
    Large   |     Blue      |    Old  
    

    So, there’s 18 possible Combinations (Variations) with Attributes

    Attributes and Variants can be dynamic

    How is this possible in SQL Server Database Query?

  • sql server: delete all the rows of all the tables
  • Using row.ColumnName on a column with spaces, after a select query generated by C# in WebMatrix
  • Create a global static variable in SQL Server?
  • Generate SQL Create Scripts for existing tables with Query
  • DbGeography make circle with center and radius
  • Logical reads for seeks on a non unique clustered index
  • 2 Solutions collect form web for “make a combination (variations) of records on the basis of a dynamic column value (attributes) using sql”

    SQL DEMO

    WITH Color as (
        SELECT V.[Value] as Color
        FROM Attributes A
        JOIN Variants V
          ON A.[AttrId]= V.[AttrId]
        WHERE A.[AttrId] = 1
    ), Size as (
        SELECT V.[Value] as Size
        FROM Attributes A
        JOIN Variants V
          ON A.[AttrId]= V.[AttrId]
        WHERE A.[AttrId] = 2
    ), Style as (
        SELECT V.[Value] as Style
        FROM Attributes A
        JOIN Variants V
          ON A.[AttrId]= V.[AttrId]
        WHERE A.[AttrId] = 3
    )     
    SELECT *
    FROM Color
    CROSS APPLY Size    
    CROSS APPLY Style;
    

    OUTPUT

    enter image description here

    Here is doing a dynamic sql. You loop from the Attributes table and create a cte and a cross join for each one. Then execute the dynamic sql. You will get the same result.

    SQL DEMO:

    CREATE PROCEDURE GetCombinations(@TableName varchar(20))  
     AS
     BEGIN
        DECLARE @sql varchar(4000);
        DECLARE @cte varchar(4000);
        DECLARE @select varchar(4000);
    
        DECLARE Cur CURSOR FOR
        SELECT [AttrId], [Title] FROM Attributes;
    
        OPEN Cur;
    
        SELECT @cte = 'WITH ';
        SELECT @select = 'SELECT * FROM ';
    
        DECLARE @AttrId VARCHAR(255);
        DECLARE @Title VARCHAR(255);
    
        FETCH NEXT FROM Cur INTO @AttrId, @Title;        
        WHILE ( @@FETCH_STATUS = 0 )
            BEGIN                                    
                SELECT @cte = @cte + @Title + ' as (' + 
                            'SELECT V.[Value] as ' + @Title + ' ' +
                            'FROM Attributes A ' +
                            'JOIN Variants V ' +
                            'ON A.[AttrId]= V.[AttrId] ' +
                            'WHERE A.[AttrId] = ' + @AttrId + 
                            '), ';         
    
                SELECT @select = @select + @Title + ' CROSS APPLY ';
    
                FETCH NEXT FROM Cur INTO @AttrId, @Title;            
            END
    
        SELECT @cte = LEFT(@cte, LEN(@cte) - 1);
        SELECT @select = LEFT(@select, LEN(@select) - 12);
    
        -- for debug --
        SELECT @sql= 'SELECT  ''' + @cte + @select + ''' as title';
    
        EXEC (@cte + @select);  
    
     END;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.