T-SQL: Consulta para Gerar Combinações com resultado UNPIVOT em linhas individuais
Problema
Eu tenho a necessidade de gerar todas as combinações com base em alguns dados, mas quanto mais eu olho para este problema, consigo progredir menos.
Imagine alguns itens que podem estar em cada posição
Posição 1 Posição 2 Posição 3
---------- ---------- ----------
1001 1003 1006
1002 1004 1007
1005
e estes dados são representados nesta tabela
declare @TabelaBase table (AlgumItemID int, PosicaoId int)
insert into @TabelaBase select 1001,1
insert into @TabelaBase select 1002,1
insert into @TabelaBase select 1003,2
insert into @TabelaBase select 1004,2
insert into @TabelaBase select 1005,2
insert into @TabelaBase select 1006,3
insert into @TabelaBase select 1007,3
Eu quero gerar todas as combinações possíveis, que neste exemplo seria
2 Posições para "Posição 1" x 3 Posições para "Posição 2" x 2 Posições para "Posição 3" = 12 combos
mas no formato 'UNPIVOT' fica assim
ComboId AlgumItemID PosicaoId
------- ---------- ----------
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
de modo que resulta em 36 linhas (12 combos x 3 Posições).
A solução precisa ser dinâmica, por isso, se eu adicionar uma outra Posição:
insert into @TabelaBase select 1008,4
ou eu adiciono qualquer número de itens:
insert into @TabelaBase select 1009,3
insert into @TabelaBase select 1010,3
insert into @TabelaBase select 1011,3
insert into @TabelaBase select 1012,3
então a mesma solução funciona. Embora eu esteja tentando evitar SQL Dinâmico.
O campo "AlgumItemID" são únicos dentro de uma posição (ou seja, são consistentes), mas não necessariamente únicos em posições cruzadas, no entanto para o propósito deste exemplo não deve importar, e você pode assumir que eles são únicos.
Note também que o valor do campo "ComboId" não é importante, é apenas um ID único que identifica cada combinação.
Solução
Em essência, eu uso uma CTE recursiva para gerar às combinações como uma cadeia delimitada - este então lê com naturalidade à dinâmica de inclusão de posições. Outra CTE sobre esta me dá um campo "ComboId" único.
À medida que os itens são adicionados na ordem certa, então eu posso dividir à string, utilizando às posições na string como os dados originais do "PosicaoId".
Esta solução utiliza o conceito de divisão de *string *em linha de Oleg Netchaev (como postado aqui: http://ask.sqlservercentral.com/questions/88772/has-anyone-tried-variations-on-8k-splitter.html). Mas para performance, eu poderia usar um UDF, apenas quero uma solução organizada, encapsulada.
;with Combo_cte as
(
select
cast(t1.AlgumItemID as varchar) Combo,
t1.PosicaoId
from @TabelaBase t1
where PosicaoId=1
union all
select
cast(Combo_cte.Combo + ';'+ cast(t2.AlgumItemID as varchar) as varchar),
t2.PosicaoId
from
Combo_cte
join @TabelaBase t2 on t2.PosicaoId = Combo_cte.PosicaoId +1
)
,ComboWithId_cte as
(
select row_number()over(order by Combo) as ComboId, Combo
from Combo_cte
where Combo_cte.PosicaoId = (select max(PosicaoId) from @TabelaBase))
select
ComboWithId_cte.ComboID,
Splitter.AlgumItemID,
Splitter.PosicaoId
from
ComboWithId_cte
cross apply (
select cast(row_number() over (order by (select null)) as int) PosicaoId,
R.Item.value('text()[1]', 'varchar(max)') AlgumItemID
from (select cast('<r>' + REPLACE(ComboWithId_cte.Combo, ';', '</r><r>') + '</r>' as xml)) X(N)
cross apply N.nodes('//r') R(Item)
) Splitter
Veja Também
- [[Portal Transact-SQL]]