• I added the top level name as an addtl. column (Level1Name) and moved the calculation to the final select (therewith removing the cte1 subquery).

    ;WITH cte

    AS

    (

    SELECT id,Name,parentid,levelid, name AS Level1Name FROM @asic b WHERE parentid IS NULL

    UNION ALL

    SELECT a.id,a.name,a.parentid,a.levelid, cte.Level1Name FROM @asic a INNER JOIN cte

    ON a.parentid=cte.id

    )

    SELECT

    MAX(Level1Name) AS name,

    COUNT(projectname) TotalProject,

    SUM(cost)TotalCost

    FROM @project p

    INNER JOIN cte ON cte.id=p.asicid

    GROUP BY p.asicid



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]