Subconjuntos De Una Tabla

[Beakdan]

Los valores de las tablas... Incluyo un num_pre con cuatro elementos para probar...

Código:

INSERT INTO Subconjuntos VALUES(1, 51, 'a')
INSERT INTO Subconjuntos VALUES(1, 52, 'b')
INSERT INTO Subconjuntos VALUES(1, 53, 'c')
INSERT INTO Subconjuntos VALUES(2, 71, 'x')
INSERT INTO Subconjuntos VALUES(2, 72, 'y')
INSERT INTO Subconjuntos VALUES(3, 91, 'l')
INSERT INTO Subconjuntos VALUES(3, 92, 'm')
INSERT INTO Subconjuntos VALUES(3, 93, 'n')
INSERT INTO Subconjuntos VALUES(3, 94, 'o')
GO
INSERT INTO Permutaciones VALUES(0, 0, 0, 0, 0)
INSERT INTO Permutaciones VALUES(1, 0, 0, 0, 1)
INSERT INTO Permutaciones VALUES(2, 0, 0, 0, 2)
INSERT INTO Permutaciones VALUES(3, 0, 0, 0, 3)
INSERT INTO Permutaciones VALUES(4, 0, 0, 0, 4)
INSERT INTO Permutaciones VALUES(1, 2, 0, 0, 2)
INSERT INTO Permutaciones VALUES(1, 3, 0, 0, 3)
INSERT INTO Permutaciones VALUES(1, 4, 0, 0, 4)
INSERT INTO Permutaciones VALUES(2, 3, 0, 0, 3)
INSERT INTO Permutaciones VALUES(2, 4, 0, 0, 4)
INSERT INTO Permutaciones VALUES(3, 4, 0, 0, 4)
INSERT INTO Permutaciones VALUES(1, 2, 3, 0, 3)
INSERT INTO Permutaciones VALUES(1, 2, 4, 0, 4)
INSERT INTO Permutaciones VALUES(1, 3, 4, 0, 4)
INSERT INTO Permutaciones VALUES(2, 3, 4, 0, 4)
INSERT INTO Permutaciones VALUES(1, 2, 3, 4, 4)
GO 

La consulta quedaría como:

Código:

WITH SubC(n, num_pre, pre_id, desc_pre, cnt) AS (
 SELECT ROW_NUMBER() OVER(PARTITION BY num_pre ORDER BY pre_id) AS n,
   num_pre, pre_id, desc_pre,
   (SELECT COUNT(*) FROM dbo.Subconjuntos WHERE num_pre = s.num_pre) AS cnt
 FROM dbo.Subconjuntos AS s
)
SELECT ROW_NUMBER() OVER(ORDER BY s1.num_pre, p.Id) AS Id,
  CAST(s1.pre_id AS VARCHAR) + 
   COALESCE(' - ' + CAST(s2.pre_id AS VARCHAR), '') + 
   COALESCE(' - ' + CAST(s3.pre_id AS VARCHAR), '') +
   COALESCE(' - ' + CAST(s4.pre_id AS VARCHAR), '') AS opc_id,
  CAST(s1.desc_pre AS VARCHAR) + 
   COALESCE(' - ' + CAST(s2.desc_pre AS VARCHAR), '') + 
   COALESCE(' - ' + CAST(s3.desc_pre AS VARCHAR), '') +
   COALESCE(' - ' + CAST(s4.desc_pre AS VARCHAR), '') AS [desc]
FROM PERMUTACIONES AS p
LEFT JOIN SubC AS s1
  ON s1.n = p.n1
  AND s1.cnt >= p.MaxN
LEFT JOIN SubC AS s2
  ON s2.n = p.n2
  AND s2.cnt >= p.MaxN
  AND s1.num_pre = s2.num_pre
LEFT JOIN SubC AS s3
  ON s3.n = p.n3
  AND s3.cnt >= p.MaxN
  AND s1.num_pre = s3.num_pre
LEFT JOIN SubC AS s4
  ON s4.n = p.n4
  AND s4.cnt >= p.MaxN
  AND s1.num_pre = s4.num_pre
WHERE s1.n is not null
 

Saludos.