This calculus can be done in only one query
(I think I am the first to demontsrate how to do that in one query only !) 😉
-- lest's assume that this table containes all datas to be permuted :
CREATE TABLE T_CMB (CMB_DATA VARCHAR(8))
-- let's assume that the joker character ; (dot comma) is not used inside the data :
INSERT INTO T_CMB VALUES ('ABC')
INSERT INTO T_CMB VALUES ('DEF')
INSERT INTO T_CMB VALUES ('GHI')
-- the following query does the permutations
WITH
T_DATA AS
(SELECT CMB_DATA, 1 AS COMBINAISON,
ROW_NUMBER() OVER(ORDER BY CMB_DATA) AS ORDRE,
COUNT(*) OVER() AS N
FROM T_CMB),
T_RECUR AS
(SELECT CAST(CMB_DATA AS VARCHAR(max)) +';' AS CMB_DATA, COMBINAISON, ORDRE, N
FROM T_DATA
UNION ALL
SELECT T1.CMB_DATA + ';' + T2.CMB_DATA, T2.COMBINAISON + 1, ROW_NUMBER() OVER(PARTITION BY T1.COMBINAISON ORDER BY T2.CMB_DATA) ORDRE, T1.N
FROM T_DATA AS T1
CROSS JOIN T_RECUR AS T2
WHERE T2.COMBINAISON < T1.N
-- this line must be delete if you want a repetitive permutation
AND T2.CMB_DATA NOT LIKE '%' + T1.CMB_DATA +';%'
),
T_COMBINE AS
(SELECT CMB_DATA, ROW_NUMBER() OVER(ORDER BY CMB_DATA) AS ORDRE
FROM T_RECUR
WHERE COMBINAISON = N),
T_N AS
(SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM T_N
WHERE N + 1 <= ALL (SELECT LEN(CMB_DATA)
FROM T_COMBINE)),
T_SOL AS
(SELECT *, REVERSE(SUBSTRING(CMB_DATA, 1, N-1)) AS SOUS_CHAINE,
REVERSE(SUBSTRING(REVERSE(SUBSTRING(CMB_DATA, 1, N-1)), 1,
CASE
WHEN CHARINDEX(';', REVERSE(SUBSTRING(CMB_DATA, 1, N-1))) - 1 = -1 THEN LEN(CMB_DATA)
ELSE CHARINDEX(';', REVERSE(SUBSTRING(CMB_DATA, 1, N-1))) - 1
END)) AS DATA
FROM T_COMBINE
INNER JOIN T_N
ON SUBSTRING(CMB_DATA, N, 1) = ';')
SELECT DATA AS CMB_DATA, ORDRE AS PERMUTATION
FROM T_SOL
CMB_DATA PERMUTATION
------------------------- --------------------
ABC 1
DEF 1
GHI 1
ABC 2
GHI 2
DEF 2
DEF 3
ABC 3
GHI 3
DEF 4
GHI 4
ABC 4
GHI 5
ABC 5
DEF 5
GHI 6
DEF 6
ABC 6
If you want a permutation with repetitive datas, simply delete the 18e line :
AND T2.CMB_DATA NOT LIKE '%' + T1.CMB_DATA +';%'
You'll get :
CMB_DATA PERMUTATION
----------------------- --------------------
ABC 1
ABC 1
ABC 1
ABC 2
ABC 2
DEF 2
ABC 3
ABC 3
GHI 3
ABC 4
DEF 4
ABC 4
ABC 5
DEF 5
DEF 5
ABC 6
DEF 6
GHI 6
ABC 7
GHI 7
ABC 7
ABC 8
GHI 8
DEF 8
ABC 9
GHI 9
GHI 9
DEF 10
ABC 10
ABC 10
DEF 11
ABC 11
DEF 11
DEF 12
ABC 12
GHI 12
DEF 13
DEF 13
ABC 13
DEF 14
DEF 14
DEF 14
DEF 15
DEF 15
GHI 15
DEF 16
GHI 16
ABC 16
DEF 17
GHI 17
DEF 17
DEF 18
GHI 18
GHI 18
GHI 19
ABC 19
ABC 19
GHI 20
ABC 20
DEF 20
GHI 21
ABC 21
GHI 21
GHI 22
DEF 22
ABC 22
GHI 23
DEF 23
DEF 23
GHI 24
DEF 24
GHI 24
GHI 25
GHI 25
ABC 25
GHI 26
GHI 26
DEF 26
GHI 27
GHI 27
GHI 27
The french version is on my blog :
http://blog.developpez.com/sqlpro?title=calculs_de_tous_les_arrangements_mathema
CU
---
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/[/url]
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l'ISEN à Toulon * * * * *