T-SQL: Query to Generate Combinations with Output Unpivoted into Individual Rows
Problem
I'm faced with a requirement to generate all the combinations, based on some data, but the harder I look at this problem, the less progress I make.
Imagine some items that can be in each position
Position 1 Position 2 Position 3
---------- ---------- ----------
1001 1003 1006
1002 1004 1007
1005
and this data is represented in this table
declare @basetable table (SomeItemID int, PositionId int)
insert into @basetable select 1001,1
insert into @basetable select 1002,1
insert into @basetable select 1003,2
insert into @basetable select 1004,2
insert into @basetable select 1005,2
insert into @basetable select 1006,3
insert into @basetable select 1007,3
I want to generate all the possible combinations which in this example would be
2 poss for Posn1 x 3 poss for Posn2 x 2 poss for Posn3 = 12 combos
but in the 'unpivoted' format of
ComboId SomeItemID PositionId
------- ---------- ----------
1 1001 1
1 1003 2
1 1006 3
2 1001 1
2 1003 2
2 1007 3
3 1001 1
3 1004 2
3 1006 3
4 1001 1
4 1004 2
4 1007 3
5 1001 1
5 1005 2
5 1006 3
6 1001 1
6 1005 2
6 1007 3
7 1002 1
7 1003 2
7 1006 3
8 1002 1
8 1003 2
8 1007 3
9 1002 1
9 1004 2
9 1006 3
10 1002 1
10 1004 2
10 1007 3
11 1002 1
11 1005 2
11 1006 3
12 1002 1
12 1005 2
12 1007 3
so that's 36 rows (12 combos x 3 positions).
The solution needs to be dynamic, so if I add another position:
insert into @basetable select 1008,4
or I add any number of items:
insert into @basetable select 1009,3
insert into @basetable select 1010,3
insert into @basetable select 1011,3
insert into @basetable select 1012,3
then the same solution works. I'm trying to avoid dynamic SQL though.
The SomeItemIDs are unique within a position (i.e. no dupes), but not necessarily unique across positions, however for the purpose of this, it shouldn't matter, and you can assume that they are unique.
Note also that the ComboId value is unimportant, it's just a unique id that identifies each combo.
Solution
In essence, I use a recursive CTE to generate the combinations as a delimited string - this then copes with the dynamic nature of adding positions. Another CTE over this one gives me a unique ComboID.
As the items are added in the right order, I can then split the string, using the positions in the string as the original PositionIds.
This solution is using Oleg Netchaev's inline string splitting solution (as posted here : http://ask.sqlservercentral.com/questions/88772/has-anyone-tried-variations-on-8k-splitter.html). But for performance I could use a UDF, I just wanted a tidy, encapsulated solution.
;with Combo_cte as
(
select
cast(t1.SomeItemId as varchar) Combo,
t1.PositionId
from @basetable t1
where positionid=1
union all
select
cast(Combo_cte.Combo + ';'+ cast(t2.SomeItemId as varchar) as varchar),
t2.PositionId
from
Combo_cte
join @basetable t2 on t2.positionid = Combo_cte.positionid +1
)
,ComboWithId_cte as
(
select row_number()over(order by Combo) as ComboId, Combo
from Combo_cte
where Combo_cte.PositionId = (select max(PositionId) from @basetable))
select
ComboWithId_cte.ComboID,
Splitter.SomeItemId,
Splitter.PositionID
from
ComboWithId_cte
cross apply (
select cast(row_number() over (order by (select null)) as int) PositionID,
R.Item.value('text()[1]', 'varchar(max)') SomeItemId
from (select cast('<r>' + REPLACE(ComboWithId_cte.Combo, ';', '</r><r>') + '</r>' as xml)) X(N)
cross apply N.nodes('//r') R(Item)
) Splitter
See Also
- [[Transact-SQL Portal]]