dimanche 5 juillet 2015

SQL 2005 CTE hierachical table, managing duplicates, without maxrecursion

The problem I am attempting to solve is a request to pull data from a table using a CTE to retrieve hierarchical data. Because of the nature of the data referencing itself, hitting max recursion is a huge problem but I don't want to arbitrarily stop the data because then it wouldn't be complete. To understand the data, here is what the sample output should look like:

Top Level
11552
     - 11414
     - 68775
            - 23667
                   - 65887*
            - 58669*
     - 58669
            - 65887*

11414
     - 58667
            - 11552*
     - 45998
            - 23667
                   - 65887*
            - 33789
     - 26998
            - 11552*
65887
     - 26998
            - 23667
                   - 65887*
            - 45998*
     - 58667
            - 11552*

The problem lies in not repeating the chain for items that have already appeared in the heirarchy but still including at least that first value (items marked by asterisk).

My initial CTE query (which includes a stop for now) is below. The query only pulls the initial data but I can't figure out how to alter it to meet the request.

WITH myCTE
AS (    
        SELECT ProcId, AsscId, 0 as [Level]
        FROM Associations 
        UNION ALL
        SELECT a.ProcId, a.AsscId, ([Level] + 1) as RecursionLevel
        FROM Associations a
        INNER JOIN myCTE m ON a.ProcId = m.ProcId
        WHERE ([Level] + 1) <= 2
    )
SELECT * FROM myCTE ORDER BY ProcId

I looked at these samples here How to use Common Table Expression and check no duplication in SQL Server and here Removing duplicates from a CTE based on a specific criteria but I am not certain either helps in this scenario.

Aucun commentaire:

Enregistrer un commentaire