SELECT y estructura del tipo arbol

antes de nada, ¿utilizas SQL Server 2005?, lo digo porque si es afirmativo
existe una sintaxis para realizar consultas recursivas de forma sencilla y
sin necesidad de montarse tablas auxiliares.

http://msdn2.microsoft.com/en-us/li...86243.aspx

Un saludo
Enrique Catala Bañuls
Solid Quality Mentors



"Cristian Meneses" wrote:

Buenas a todos
Tengo una tabla para introducir valores y obtener una estructura del
tipo arbol.
La idea es que un padre pueda tener muchos "hijos", y guardo el valor
de nivel para acelerar luego un select para obtener todo ordenado tal
como lo mostraria en un control del tipo treeview.
En el caso de los primeros padres IDPadre = NULL y Nivel = 1, el valor
de Numero es controlado en el insert para que no se repitan, y pueden
haber hijos con numero 1, 2, 3, 8, 15, etc.. sucesivamente.
La duda surge al hacer este select, ya que si ordeno por Nivel,
IDPadre y Numero no obtengo el contenido como lo deseo. Alguna
sugerencia?
Dejo a continuacion las sentencias sql para que lo prueben...


if NOT (object_id('tempdb.dbo.#TablaAux') is null)
DROP TABLE #TablaAux

CREATE TABLE #TablaAux
(
IDH int IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
IDPadre int NULL,
Descripcion varchar(50) NOT NULL,
Numero int NOT NULL,
Nivel tinyint NOT NULL,
Eliminado bit NOT NULL
)

INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (NULL, 'Padre1', 1, 1, 0) --ID 1
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (NULL, 'Padre2', 2, 1, 0) --ID 2
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (NULL, 'Padre3', 3, 1, 0) --ID 3
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (1, 'Hijo11', 1, 2, 0) --ID 4
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (1, 'Hijo12', 2, 2, 0) --ID 5
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (2, 'Hijo21', 1, 2, 0) --ID 6
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (2, 'Hijo22', 2, 2, 0) --ID 7
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (3, 'Hijo31', 1, 2, 0) --ID 8
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (4, 'Nieto111', 1, 3, 0) --ID 9
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (4, 'Nieto112', 2, 3, 0) --ID 10
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (7, 'Nieto221', 1, 3, 0) --ID 11
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (7, 'Nieto222', 2, 3, 0) --ID 12
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (8, 'Nieto311', 1, 3, 0) --ID 13
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (8, 'Nieto315', 5, 3, 0) --ID 14
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel, Eliminado)
VALUES (8, 'Nieto318', 8, 3, 0) --ID 15

SELECT * FROM #TablaAux


Muchas gracias por adelantado...



Cristian Meneses

Hola Enrique
Gracias por tu pronta respuesta. Lamentablemente no para esta base de
datos.
Uso SQL2000 MSDE.
Muchas gracias nuevamente


Cristian Meneses

Buenas a todos
Tengo una tabla para introducir valores y obtener una estructura del
tipo arbol.
La idea es que un padre pueda tener muchos "hijos", y guardo el valor
de nivel para acelerar luego un select para obtener todo ordenado tal
como lo mostraria en un control del tipo treeview.
En el caso de los primeros padres IDPadre = NULL y Nivel = 1, el
valor
de Numero es controlado en el insert para que no se repitan, y pueden
haber hijos con numero 1, 2, 3, 8, 15, etc.. sucesivamente.
La duda surge al hacer este select, ya que si ordeno por Nivel,
IDPadre y Numero no obtengo el contenido como lo deseo. Alguna
sugerencia?
Dejo a continuacion las sentencias sql para que lo prueben...

if NOT (object_id('tempdb.dbo.#TablaAux') is null)
DROP TABLE #TablaAux


CREATE TABLE #TablaAux
(
IDH int IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
IDPadre int NULL,
Descripcion varchar(50) NOT NULL,
Numero int NOT NULL,
Nivel tinyint NOT NULL,
Eliminado bit NOT NULL
)


INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (NULL, 'Padre1', 1, 1, 0) --ID 1
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (NULL, 'Padre2', 2, 1, 0) --ID 2
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (NULL, 'Padre3', 3, 1, 0) --ID 3
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (1, 'Hijo11', 1, 2, 0) --ID 4
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (1, 'Hijo12', 2, 2, 0) --ID 5
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (2, 'Hijo21', 1, 2, 0) --ID 6
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (2, 'Hijo22', 2, 2, 0) --ID 7
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (3, 'Hijo31', 1, 2, 0) --ID 8
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (4, 'Nieto111', 1, 3, 0) --ID 9
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (4, 'Nieto112', 2, 3, 0) --ID 10
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (7, 'Nieto221', 1, 3, 0) --ID 11
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (7, 'Nieto222', 2, 3, 0) --ID 12
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (8, 'Nieto311', 1, 3, 0) --ID 13
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (8, 'Nieto315', 5, 3, 0) --ID 14
INSERT INTO #TablaAux (IDPadre, Descripcion, Numero, Nivel,
Eliminado)
VALUES (8, 'Nieto318', 8, 3, 0) --ID 15


SELECT * FROM #TablaAux


Muchas gracias por adelantado...


Cristian Meneses
SQL2000

Hola Cristian,

On 28 apr, 01:47, Cristian Meneses wrote:

Hola Enrique
Gracias por tu pronta respuesta. Lamentablemente no para esta base de
datos.
Uso SQL2000 MSDE.
Muchas gracias nuevamente

Me he puesto a jugar un poco con tu problema y esto es lo que me
ha salido. Espero te ayude.

He pensado que no te vas a salvar de tener que solucionar el
problema procedimentalmente. Entonces me he puesto a programar una
función que devuelve una tabla con dos columnas, el IDH de tu tabla
y el orden que le corresponde. Después se puede hacer un join de
este resultado, donde estará el orden, con tu tabla.

A esta función le pasas el padre del que quieres incluir todos sus
descendientes; en el caso general null. La función introduce los
registros en la tabla en el orden requerido y la columna orden como
es identity irá generando ese orden.

Por cierto la tabla temporal que tu has dado la he puesto en una
tabla normal con el nombre TablaAux, para utilizarla en la función.

Aquí está la función:
-
create function OrdenArbol(@idp int)
returns @t Table (idh int, orden int identity(1,1))
as
begin
declare @idh int
declare cur cursor for
select idh
from tablaaux
where isnull(@idp,0)=isnull(idpadre,0)
order by numero
open cur
fetch next from cur into @idh
while @@fetch_status=0
begin
insert into @t (idh) values (@idh)
insert into @t (idh) select idh from dbo.OrdenArbol(@idh)
fetch next from cur into @idh
end
close cur
deallocate cur
return
end

Ahora puedes utilizarla así:

select Aux.*
from TablaAux Aux
join dbo.OrdenArbol(null) Arb on Aux.idh=Arb.idh
order by Arb.Orden

O si quieres, por ejemplo, solo los descendientes de idh = 1 puedes
hacer así:

select Aux.*
from TablaAux Aux
join dbo.OrdenArbol(1) Arb on Aux.idh=Arb.idh
order by Arb.Orden

y para ver solo el resultado de la función:

select * from dbo.OrdenArbol(null)

Suerte con el asunto.

Saludos,
Carlos

Hola otra vez Cristian,

Me he olvidado de decirte que la columna nivel no la necesitas
para nada en tu tabla original porque tambien la puede calcular
la función.

Aquí te pongo una segunda versión de la función,
donde también se hace eso.

-
create function OrdenArbol2(@idp int, @nivel int)
returns @t Table (idh int, orden int identity(1,1), nivel int)
as
begin
declare @idh int
declare cur cursor for
select idh
from tablaaux
where isnull(@idp,0)=isnull(idpadre,0)
order by numero
set @nivel = @nivel + 1
open cur
fetch next from cur into @idh
while @@fetch_status=0
begin
insert into @t (idh, nivel)
values (@idh, @nivel)
insert into @t (idh, nivel)
select idh,nivel from dbo.OrdenArbol2(@idh, @nivel)

fetch next from cur into @idh
end
close cur
deallocate cur
return
end


Prueba ahora esta consulta:

select *,
replicate(' ',Arb.nivel - 1) + Aux.Descripcion as Descr2
from TablaAux Aux
join dbo.OrdenArbol2(null,0) Arb on Aux.idh=Arb.idh
order by Arb.Orden

Como ves, el nivel calculado por la función es el mismo que el que
estaba en la tabla original.

He añadido también a esta última consulta la columna Descr2 como
un ejemplo de como puedes utilizar el nivel.

Aclarar también (porque no sé si lo he entendido bien) que dentro
del mismo nivel (los hijos del un mismo padre) la función usa la
columna 'numero' para ordenar. Pero para esto podrías utilizar
otra columna claro, por ejemplo 'Descripcion'.

En todo caso espero haber entendido bien y que te sea de ayuda
todo esto.

Saludos,
Carlos