Aggregate total Descendants using CTE MS SQL

I have a very simple table in a Micrososft SQL 2008 R2 database called "Ancestry" of the form:

parentSearch childSearch

1195784 1185596

1195787 1185596

1195787 1195785

1195787 1195786

1195799 1185596

1185596 1195785

1195785 1195786

1195786 1195776

1195786 1195788

1195786 1195790

1195786 1195796

1195786 1195798

1195785 1195786

1195786 1195776

1195786 1195788

1195786 1195790

1195786 1195796

1195786 1195798

1195786 1195776

1195786 1195788

1195786 1195790

1195786 1195796

1195786 1195798

This is an ancestry table, with descendants at least four levels deep. I have beat myself blue inthe fac开发者_C百科e trying to write a CTE against this that will aggregate the total number of descendants for EACH parent, nom atter the level they are at. A single child in this data can have multiple parents.

Try this:

with temp as
(
SELECT 1195784 as Parent, 1185596 as Child
UNION
SELECT 1195787, 1185596
UNION
SELECT 1195787, 1195785
UNION
SELECT 1195787, 1195786
UNION
SELECT 1195799, 1185596
UNION
SELECT 1185596, 1195785
UNION
SELECT 1195785, 1195786
UNION
SELECT 1195786, 1195776
UNION
SELECT 1195786, 1195788
UNION
SELECT 1195786, 1195790
UNION
SELECT 1195786, 1195796
UNION
SELECT 1195786, 1195798
UNION
SELECT 1195785, 1195786
UNION
SELECT 1195786, 1195776
UNION
SELECT 1195786, 1195788
UNION
SELECT 1195786, 1195790
UNION
SELECT 1195786, 1195796
UNION
SELECT 1195786, 1195798
UNION
SELECT 1195786, 1195776
UNION
SELECT 1195786, 1195788
UNION
SELECT 1195786, 1195790
UNION
SELECT 1195786, 1195796
UNION
SELECT 1195786, 1195798
),
agg as
(
        SELECT  parent, child
        FROM    temp
        UNION ALL
        SELECT  t.parent, t.Child
        FROM    agg
        JOIN    temp t
        ON      agg.child = t.parent
)

SELECT parent, COUNT(*)
FROM agg
GROUP BY parent

Try this:

WITH CTE AS
(
    SELECT *
    FROM Ancestry
    UNION ALL
    SELECT A.ParentSearch, B.ChildSearch
    FROM CTE A
    INNER JOIN Ancestry B
    ON A.ChildSearch = B.ParentSearch
)
SELECT ParentSearch, COUNT(*) Quant
FROM CTE
GROUP BY ParentSearch
ORDER BY ParentSearch
OPTION(MAXRECURSION 0)