Recursive CTE to get top level results

Question

Here is my current data set.

| BIMUnique | Description                                | Quantity | SuperiorUnique | LineCode |
|-----------|--------------------------------------------|----------|----------------|-----------|
| 660084    | Top Level Order Description                | 1        | 0              | 01        |
| 660085    | Second Level Order Description             | 50       | 660084         | 01        |
| 660086    | Second Level Order Description w/sub order | 200      | 660084         | 02        |
| 660087    | Third Level Order Description              | 10       | 660086         | 07        |

I would like to get something like this

| Top Level Description       | Immediate Parent                           | Item Description                           | Navigation (LineCode Concatenation) | Qty |
|-----------------------------|--------------------------------------------|--------------------------------------------|-------------------------------------|-----|
| Top Level Order Description | 0                                          | Top Level Order Description                | 01                                  | 1   |
| Top Level Order Description | Top Level Order Description                | Second Level Order Description             | 01.01                               | 50  |
| Top Level Order Description | Top Level Order Description                | Second Level Order Description w/sub order | 01.02                               | 200 |
| Top Level Order Description | Second Level Order Description w/sub order | Third Level Order Description              | 01.02.07                            | 10  |

My current CTE has two issues - first it doesn't show the Top Level parent, only the immediate. And second the ROW_NUMBER just counts rows and doesn't reflect the LineCode. If my end users create 3 list items and then delete item number 2 the system doesn't go back and reorder the line numbers.

WITH bi AS 
    (
        SELECT  
          m.*, 
          CAST(ROW_NUMBER() OVER (ORDER BY m.LineCode) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS Tree
        FROM BidItems m with (nolock)
        WHERE m.SuperiorUnique = 0 AND m.JobUnique = '12591'

        UNION ALL

        SELECT  
          m.*,  
          bi.Tree + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.SuperiorUnique ORDER BY m.LineCode) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
        FROM BidItems m with (nolock)
        JOIN bi ON m.SuperiorUnique = bi.BIMUnique
        WHERE m.JobUnique = '12591'
    )

SELECT 
  Job.Number,
  Job.Description,
  bi.Tree,
  bi.LineCode,
  bi.Description,
  bi.Quantity,
  bi.TotalCosts,
  bi.*
FROM Job AS job with (nolock)
INNER JOIN bi ON bi.JobUnique = Job.JOBUnique
INNER JOIN BidItems AS sup with (nolock) ON bi.SuperiorUnique = sup.BIMUnique
LEFT JOIN BidItemDetail AS bid with (nolock) ON bid.BidItemUnique = bi.BIMUnique

ORDER BY Bi.Tree

And we are on MSSQL 2012

Updated: LineOrder should be LineCode.

GMB · Accepted Answer · 2020-03-05T01:19:18.343

0

Consider the following query, that traverses the tree from the root to the leafs. I don't really see the need for row_number() to generate the paths, which is apparently made of LineNumbers.

with cte (TopLevelDescription, ImmediateParent, ItemDescription, Navigation, Qty, BIMUnique)
as (
    select 
        Description, 
        cast(0 as varchar(60)), 
        Description, 
        cast(LineOrder as varchar(max)), 
        Qty, 
        BIMUnique 
    from BidItems
    where SuperiorUnique = 0
    union all
    select 
        c.TopLevelDescription, 
        c.ItemDescription, 
        b.Description, 
        c.Navigation + '.' + b.LineOrder, 
        b.Qty, 
        b.BIMUnique
    from cte c
    inner join BidItems b on b.SuperiorUnique = c.BIMUnique
)
select * from cte

Demo on SQL Server 2012:

TopLevelDescription         | ImmediateParent                            | ItemDescription                            | Navigation | Qty | BIMUnique
:-------------------------- | :----------------------------------------- | :----------------------------------------- | :--------- | --: | --------:
Top Level Order Description | 0                                          | Top Level Order Description                | 1          |   1 |    660084
Top Level Order Description | Top Level Order Description                | Second Level Order Description             | 1.1        |  50 |    660085
Top Level Order Description | Top Level Order Description                | Second Level Order Description w/sub order | 1.2        | 200 |    660086
Top Level Order Description | Second Level Order Description w/sub order | Third Level Order Description              | 1.2.7      |  10 |    660087

edited Mar 05 '20 at 01:19

answered Mar 04 '20 at 23:57

GMB

216,147
25
84
135

I get these two errors. I assume now it's some sort of data type mismatch? Types don't match between the anchor and the recursive part in column "ImmediateParent" of recursive query "cte". Types don't match between the anchor and the recursive part in column "Navigation" of recursive query "cte". – user2962869 Mar 05 '20 at 00:28
@user2962869: you might need some casting... What are the datatypes of columns `ItemDescription` and `LineOrder`? – GMB Mar 05 '20 at 00:31
BIMUnique is int, LineOrder is varchar(12), Description is varchar(60) and SuperiorUnique is int. I already tried CONVERT(varchar(10), bi.Navigation) + CONVERT(varchar(10), m.LineCode) and I still get the error. – user2962869 Mar 05 '20 at 00:43
@user2962869: I slightly modified the query. Please give it another try. – GMB Mar 05 '20 at 00:49
I get the same error - Types don't match between the anchor and the recursive part in column "ImmediateParent" of recursive query "cte" and Types don't match between the anchor and the recursive part in column "Navigation" of recursive query "cte". – user2962869 Mar 05 '20 at 01:07
@user2962869: fixed for real now - and tested. See my updated answer. – GMB Mar 05 '20 at 01:19
That's it! Thanks for the help – user2962869 Mar 05 '20 at 15:35
Welcome @user2962869! If my answer was helpful to you, you may also upvote it using the upper arrow... Thanks. – GMB Mar 05 '20 at 15:43

Recursive CTE to get top level results

1 Answers1