Raimundo,
No contamos con una sentencia (al menos en T-SQL) que permita hacer esta operacion a nivel de una jerarquia. Una forma de lograrlo es expandiendo la relacion de cada padre con todos sus descendientes, lo cual es conocido como Transitive Closure en
la teoria de grafos. En este caso en particular la relacion tambien se expande a un nodo consigo mismo.
Ejemplo:
Padre Descendiente
A A
A A1
A A2
A A3
A A11
A A111
A A112
A A12
A B
A C
A1 A1
A1 A11
A1 A12
A11 11
A11 A111
A11 A112
A111 A111
A112 A112
A12 A12
A2 A2
A2 C
A3 A3
A3 B
B B
C C
Ahora puedes usar este resultado para unir con tu tabla mediante la relacion (Descendiente, Componentes) y agrupar por el padre.
Ejemplo con la raiz (A)
Padre Descendiente
A A
A A1
A A2
A A3
A A11
A A111 - 0.2
A A112 - 0.2
A A12 - 0.6
A B - 0.5
A C - 0.6
==============
0.6 + 0.5 + 0.6 + 0.2 + 0.2 = 2.10
Puedes usar una CTE recursiva para crear el Transitive Closure pero preferible que este se materialize para que puedas adicionar indices que soporten la operacion de union.
Aca te dejo el codigo, el cual es un poco intimidante pero se debe principalmente a que has unido en una sola relacion la jerarquia y el costo de los articulos.
DECLARE @articulos table (
articulo varchar(4),
nivel int,
componentes varchar(4),
coste decimal(5,2)
);
INSERT INTO @articulos
VALUES
('A',10000,'A1',0),
('A',20000,'A2',0),
('A',20001,'A3',0),
('A1',10000,'A11',0),
('A1',10005,'A12',0.6),
('A11',10000,'A111',0.2),
('A11',10010,'A112',0.2),
('A3',10000,'B',0.5),
('A2',10000,'C',0.6);
WITH A AS (
SELECT
articulo AS padre,
articulo AS descendiente
FROM
@articulos
UNION
SELECT
componentes,
componentes
FROM
@articulos
)
, TC AS (
SELECT
padre,
A.descendiente
FROM
A
UNION ALL
SELECT
P.padre,
C.componentes
FROM
TC AS P
INNER JOIN
@articulos AS C
ON C.articulo = P.descendiente
)
, Agg AS (
SELECT
TC.padre,
SUM(A.coste) AS sum_coste
FROM
TC
INNER JOIN
@articulos AS A
ON A.componentes = TC.descendiente
GROUP BY
TC.padre
)
, H AS (
SELECT DISTINCT
articulo AS descendiente,
CAST(NULL AS varchar(4)) AS padre,
0 AS lvl,
CAST(1 AS varbinary(900)) AS [path]
FROM
@articulos AS A
WHERE
NOT EXISTS (
SELECT
*
FROM
@articulos AS B
WHERE
B.componentes = A.articulo
)
UNION ALL
SELECT
C.componentes,
C.articulo,
P.lvl + 1,
CAST(P.[path] + CAST(ROW_NUMBER() OVER(PARTITION BY C.articulo ORDER BY C.componentes) AS binary(4)) AS varbinary(900))
FROM
H AS P
INNER JOIN
@articulos AS C
ON C.articulo = P.descendiente
)
SELECT
REPLICATE('||||||||', lvl) + H.descendiente AS articulo,
Agg.sum_coste
FROM
H
INNER JOIN
Agg
ON Agg.padre = H.descendiente
ORDER BY
[H].[path];
GO
/*
articulo sum_coste
A 2.10
||||||||A1 1.00
||||||||||||||||A11 0.40
||||||||||||||||||||||||A111 0.20
||||||||||||||||||||||||A112 0.20
||||||||||||||||A12 0.60
||||||||A2 0.60
||||||||||||||||C 0.60
||||||||A3 0.50
||||||||||||||||B 0.50
*/
Puedes leer mas al respecto en el libro de Itzik Ben-Gan sobre
T-SQL Querying
AMB
Some guidelines for posting questions...
AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas
