SQL Server Get All Combinations in Relation Table without Loop or Cursors
sql server loop through table rows without cursor
sql loop through select results
sql server loop through table and update
sql loop through table and insert into another table
loop through user defined table type in sql server
sql server foreach table
cursor in sql server
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:
Id |Value 1 | Color 2 | Size 3 | Length
Id | VariationId | Value 1 | 1 | Black 2 | 1 | Red 3 | 2 | Large 4 | 2 | Small 5 | 2 | Medium 6 | 3 | Tall
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.
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
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.
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
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
SQL Server Loop through Table Rows without Cursor, SQL Server Loop through Table Rows without Cursor We all know that SQL Server, like every relational database allows the If you have ever worked with cursors, you may find this title a bit confusing because after all,� Using a While Loop Instead of Cursors in SQL Server. If you have ever worked with cursors, you may find this title a bit confusing because after all, cursors uses while constructs to iterate between rows. But besides that, I want to show you that in some circumstances when we use a cursor to iterate over a set of rows we can change it to a
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
3 ways to improve T-SQL performance, The Common Table Expressions started in SQL Server 2005 and they can be used to replace cursors or the while loop. It is also used for� SQL Server has a couple of undocumented system stored procedures in the master database, which allow you to loop through all or selected databases using the sp_MSforeachdb system stored procedure or loop through all or selected user tables using the sp_MSforeachtable system stored procedure.
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
6 Performing SQL Operations from PL/SQL, Querying Data with PL/SQL: Explicit Cursor FOR Loops By extending SQL, PL/ SQL offers a unique combination of power and ease of use. NUMBER; BEGIN -- The NEXTVAL value is the same no matter what table you select from. and refer to those identifiers from other rows to set up parent-child relationships. Note: If you are using SQL Server you would be better served using: WHILE EXISTS(SELECT * FROM #Temp) Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one (see Josef's answer below).
10 SQL tricks that you didn't think were possible, Should the two tables be joined in a nested loop or with a hashmap? Establish a formal foreign key relationship between the tables (this tells the database that every SQL Server, PostgreSQL, some others: INSERT INTO my_table(a) I.e. the second subquery is allowed to select from the very CTE that� The SQL Server cursor is T-SQL logic, which allows us to loop through the related query result. This enables us to take the actions sequentially – e.g., perform an update on a single row. Sometimes this could (seem to) be helpful, but when working with databases, you shouldn’t use procedural programming patterns but rather stick to
Working with SQL Cursors, In SQL cursors serve as a pointer that enables application programming The data in relational database are managed in the form of sets. As a result, query results return by SQL SELECT statements are referred to as result sets. It is possible to declare more than one cursor and nest them in loops. Transact-SQL cursors and API cursors have different syntax, but the following general process is used with all SQL Server cursors: Associate a cursor with the result set of a Transact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.
Performing SQL Operations from PL/SQL, By extending SQL, PL/SQL offers a unique combination of power and ease of use . is the same no matter what table you select from -- You usually use NEXTVAL to refer to those identifiers from other rows to set up parent-child relationships. open the cursor before fetching LOOP FETCH c1 INTO v_lastname, v_jobid;� Cross joins are used to return every combination of rows from two tables, this sometimes called a Cartesian product. In SQL Server you can use the CROSS JOIN keywords to define a cross join. Simple CROSS JOIN Example. Below is an example of a simple select statement with a CROSS JOIN clause.
- Are you talking about a
- @Larnu Cross Join won't work here, see updated question, and please vote on reopening the question since it is not a duplicate.
- @MozartAlKhateeb I can't understand this line 2 | 1 (Color) | 2 (Black) I think it's Red instead of Black
- Yes I've updated it.
- 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.
- 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.
- @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.
- 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.
- 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'.
- 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 ?"
- 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?
- It can be any Id, but the point is to have unique to each set of dimensions.