al cliente lo que pida:
Código SQL:
Ver originalDECLARE @temp TABLE
(
id INT,
codigo VARCHAR(20),
padreid INT
)
INSERT INTO @temp VALUES (1 , '5' , NULL)
INSERT INTO @temp VALUES (2 , '6-2' , 7)
INSERT INTO @temp VALUES (3 ,'6-1' , 7)
INSERT INTO @temp VALUES (4 ,'5-1' , 1)
INSERT INTO @temp VALUES (5 ,'5-1-1' , 4)
INSERT INTO @temp VALUES (6 ,'5-2' , 1)
INSERT INTO @temp VALUES (7 ,'6' , NULL)
DECLARE @temp2 TABLE
(
id INT,
padreid INT,
codigo VARCHAR(20),
padre2 INT,
id2 INT,
codigo2 VARCHAR(20)
)
INSERT INTO @temp2
SELECT
t1.id,t1.padreid,t1.codigo,t2.padreid AS padre2,t2.id AS id2,t2.codigo AS codigo2
FROM @temp AS t1
LEFT JOIN @temp AS t2 ON (t1.id=t2.padreid)
WHERE t2.padreid IS NOT NULL
SELECT id AS padre ,padreid AS id,codigo FROM (
SELECT *,ROW_NUMBER() OVER(partition BY codigo ORDER BY id) AS rn FROM
(
SELECT id,padreid,codigo FROM @temp2
UNION
SELECT padre2,id2,codigo2 FROM @temp2
) AS query
) query2 WHERE rn=1
como son variables de tipo tabla se ejecuta todo el script de una sola vez :) y el resultado es:
padre id codigo
----------- ----------- --------------------
1 NULL 5
1 4 5-1
4 5 5-1-1
1 6 5-2
7 NULL 6
7 3 6-1
7 2 6-2