CODEWITHSUNDEEP
sqlsql-server
dynamphorous
dynamphorous

Reputation: 769

SQL Order One Column By Value in Another

I am trying to order the results from SQL so that an indented BOM nests the values in the correct order. I got the "hard" recursive part done, but now getting the results to nest correctly is proving to be more problematic than I would have imagined. There do not seem to be any duplicates of this exact question in my rather extensive research thus far.

Here is my data:

+------+------------+-------+-----+-------+
| Root | Assemblys  | Items | Qty | Depth |
+------+------------+-------+-----+-------+
| Root | Assembly   | Item1 | 1   |     0 |
| Root | Item1      | Item2 | 3   |     1 |
| Root | Item1      | Item3 | 4.6 |     1 |
| Root | Item2      | Item4 | 1.5 |     2 |
| Root | Item2      | Item5 | 22  |     2 |
| Root | Item3      | Item6 | 6   |     2 |
+------+------------+-------+-----+-------+

What I'm looking to do is sort the columns so that one column follows the values from another column. Notice how Item1 has its assemblies moved to be right after it appears in the items row? I have tried to figure out how to use one column to sort another column, but so far no luck with anything I've tried.

+------+-----------+-------+-----+-------+
| Root | Assemblys | Items | Qty | Depth |
+------+-----------+-------+-----+-------+
| Root | Assembly  | Item1 | 1   |     0 |
| Root | Item1     | Item2 | 3   |     1 |
| Root | Item2     | Item4 | 1.5 |     2 |
| Root | Item2     | Item5 | 22  |     2 |
| Root | Item1     | Item3 | 4.6 |     1 |
| Root | Item3     | Item6 | 6   |     2 |
+------+-----------+-------+-----+-------+

Here is a SQL Fiddle

Upvotes: 1

Views: 916

Answers (1)

roman
roman

Reputation: 117606

You can use recursive CTE, something like this:

;with cte as (
   select *, cast([Item] as nvarchar(max)) as [path] from IndentedBOM where Depth = 0

   union all

   select i.*, c.[path] + '/' + i.[Item]
   from cte as c
       inner join IndentedBOM as i on i.[Assembly] = c.[Item]
)
select *
from cte
order by [path]

sql fiddle demo

Upvotes: 2

Related Questions