Consulta SQL para realizar vista de arbol

[Libras]

Código SQL:

CREATE TABLE #temp
(
area VARCHAR(20),
subarea VARCHAR(50),
cp VARCHAR(20),
numero INT,
extra VARCHAR(20)
)
 
INSERT INTO #temp VALUES ('Brabante','Nord Eindhoven','1186VZ',5,'') 
INSERT INTO #temp VALUES ('Brabante','Nord Eindhoven','1186VZ',7,'')
INSERT INTO #temp VALUES ('Brabante','Nord Eindhoven','1186VZ',9,'')
INSERT INTO #temp VALUES ('Brabante','Nord Eindhoven','1186VZ',9,'A')
INSERT INTO #temp VALUES ('Brabante','Nord Eindhoven','1186VZ',11,'')
INSERT INTO #temp VALUES ('Brabante','Zuid Eindhoven','1195AS',232,'')
 
CREATE TABLE #temp2
(
cp VARCHAR(20),
numero INT,
extra VARCHAR(20),
STATUS VARCHAR(20)
)
 
INSERT INTO #temp2 VALUES ('1186VZ',5,'','Completed')
INSERT INTO #temp2 VALUES ('5836AB',12,'','Completed')
 
 
SELECT area, subarea,COUNT(subarea) total ,SUM(clientes) clientes FROM(
SELECT 
area,subarea,
CASE WHEN isnull(t2.numero,0)=0 THEN 0 ELSE 1 END AS clientes
FROM #temp AS t1
FULL OUTER JOIN #temp2 AS t2 ON (t1.cp=t2.cp AND t1.numero=t2.numero AND t1.extra=t2.extra)
) t3 
WHERE area IS NOT NULL
GROUP BY area,subarea
 

con algo como eso ;)