Subconjuntos De Una Tabla

[editzon]

saludos a todos, gracias por leer este mensaje

ayuda por favor

tengo una tabla con la siguiente informacion
num_pre pre_id desc_pre
-----------------------------------
1 51 a
1 52 b
1 53 c
2 71 x
2 72 y

necesito generar todos los subconjuntos para una agrupacion segun la columna num_pre osea, para cada fila que tenga igual num_pre generar todos los subconjuntos posibles, de esta manera

id opc_id desc
-------------------------------
1 51 a
2 52 b
3 53 c
4 51-52 a-b
5 51-53 a-c
6 52-53 b-c
7 51-52-53 a-b-c
8 71 x
9 72 y
10 71-72 x-y

he estado tratando de generar combinaciones pero estoy muy enredado con eso.

Muchas gracias a todos por su colaboracion

[Beakdan]

Bueno, tu problema es de lo más interesante, me tuvo pensando un buen rato, pero la solución se simplifica con la ayuda de una tabla auxiliar.
Si intentas generar las distintas combinaciones con T-SQL vas a tener un problema bastante serio y te convendría mejor hacerlo de otra manera.
Sin embargo, si tuvieras una tabla auxiliar con las permutaciones el problema se reduce a una serie de joins en una consulta. Ahora bien, es de notar que el numero de subconjuntos posibles para un conjunto dado es 2 a la n (n el número de elementos). Por lo tanto, es muy importante saber de antemano la máxima cantidad de elementos existentes para cada num_pre. Esto determina el tamaño de la tabla auxiliar y determina además la cantidad de joins que se deben indicar en la consulta.
Para que el resultado sea realmente correcto, en el resultado para cada num_pre debe haber también un resultado nulo (se incluye una permutación para lograrlo), ya que el conjunto vacío es subconjunto de todo conjunto. Este punto debe ser posible, sólo que el query se tornaría más complejo.
El orden de los subconjuntos es irrelevante en teoría, pero se ve muy bien cuando la secuencia del resultado es monótona. Esto es fácil de conseguir con un campo de orden en la tabla auxiliar.
Traté de hacer un algoritmo que generara las secuencias que deben existir en la tabla auxiliar (con C++), pero he terminado con un enredo recursivo que no devuelve todos los valores como espero, así que la solución que te publico aquí implica construir "a mano" la tabla auxiliar. Para cuatro elementos en cada grupo una tabla con 16 registros basta.
Ah, la solución depende completamente de las características disponibles en SQL Server 2005. Para SQL Server 2000, sería un poco más complicado, pero posible.

Código:

CREATE TABLE Subconjuntos(
  num_pre INT,
  pre_id INT,
  desc_pre CHAR(1)
)
GO
 
CREATE TABLE Permutaciones(
  Id SMALLINT IDENTITY(1,1) NOT NULL,
  n1 SMALLINT NOT NULL,
  n2 SMALLINT NOT NULL,
  n3 SMALLINT NOT NULL,
  n4 SMALLINT NOT NULL,
  MaxN SMALLINT NOT NULL
)
GO 

[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.

[editzon]

Gracias por tu ayuda, funciona de maravilla, tengo otra consulta parecida a la anterior. Tengo otra tabla de nombre A con la siguiente informacion

a_id o_id p_id
-------------------------------------------------
1 51 1
1 52 1
1 53 1
1 71 2
1 72 2
2 51 1
2 52 1
2 73 2


generar otra tabla con la concatenacion de la columna pre_id para una agrupacion por las columnas a_id y p_id de la siguiente manera

a_id o_id p_id
-------------------------------------
1 51-52-53 1
1 71-72 2
2 51-52 1
2 73 2

Tengo una solucion usando while anidados pero es muy ineficiente ya la tabla A contiene 700000 filas y tomo 1 hora procesarla. He estado tratando de buscar otra solucion mas eficiente pero no se me ocurre nada brillante

Gracias por tu colaboracion

[Beakdan]

Esta es más fácil. Podrías crear una función que concatene en dependiendo de los valores a_id y p_id que indiques.
O bien usar CROSS APPLY y XQuery:

Código:

SELECT     dis.a_id,
         LEFT(res.o_id, LEN(res.o_id) - 3) AS o_id, --3 ES LA LONGITUD DE LA CADENA ' - '
         dis.p_id
FROM       (
             SELECT  DISTINCT a_id, p_id
             FROM    A
         ) AS dis
CROSS APPLY (
             SELECT  CAST(o_id AS VARCHAR(12)) + ' - ' AS '*'
             FROM    A
             WHERE   a_id = dis.a_id AND p_id = dis.p_id
             FOR XML PATH('')
          ) AS res(o_id) 

Por cierto... ¿qué clase de aplicación estás haciendo? Lo que solicitas es poco común.

[editzon]

Gracias Beakdan por tu ayuda tu solucion es realmente elegante, la verdad es que no conozco muy bien las nuevas posibilidades de sql server 2005. Yo tenia unas 200 lineas de código para hacer lo mismo , pero sin rendimiento, tu solución sin duda se llama eficiencia.

Te comento que todo lo anterior para una bodega de datos en donde necesito guardar una encuesta con diferentes preguntas de multiple respuesta, y necesito mostrar la cadena concatenada de opciones marcadas por una persona para cada una de las diferentes preguntas . O sea si para la pregunta 1 marco las opciones a, b, c, necesito la respuesta concatenada de esas opciones a-b-c para guardarla en otra tabla con la primera columna el numero de la pregunta (1)y la segunda columna con la cadena de opciones concatenada (a-b-c).

De nuevo gracias por tu ayuda