1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| DECLARE @Count Int;
DECLARE @Noms nvarchar(4000);
SET @Noms = 'S1,S2,S3';
SET @Count = (SELECT COUNT(*) FROM FT_Split(@Noms,','));
WITH Combinaisons ( Level, Id ) AS
(
SELECT @Count, CAST(Table.Id AS nvarchar(21)) FROM Table WHERE (Table.Nom = (SELECT Items FROM FT_Split(@Noms, ',') WHERE (Id = @Count)))
UNION ALL
SELECT Level - 1, CAST(Combinaisons.Id AS nvarchar(10)) + ',' + CAST(Table.Id AS nvarchar(10))
FROM Combinaisons CROSS JOIN dbo.Table
WHERE (Table.Nom = (SELECT Items FROM FT_Split(@Noms, ',') WHERE (Id = Level-1))) AND Level > 0
)
SELECT * FROM Combinaisons WHERE (Level = 1)
OPTION ( MAXRECURSION 500 ) |
Partager