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.

Answer 1

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