CODEWITHSUNDEEP
sqlsql-serverbinary-tree
user1866308
user1866308

Reputation: 146

How to identify leaf node from multi column table?

I have a table with multiple column for category/sub-categories

multi level table

I would like to identify the rows that are leaf nodes. Leaf nodes could be on any level for instance the 11th one for dresses is a leaf node as it has no child. How can I achieve this in MS SQL?

Upvotes: 0

Views: 190

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You can use not exists. SQL Server now has a convenient function concat_ws() that is helpful here:

with t as (
      select t.*,
             concat_ws('->', group_1, group_2, group_3, group_4) as groups
      from <table> t
     )
select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.groups like concat(t.groups, '->%')
                 );

This is easy enough without concat_ws() as well:

with t as (
      select t.*,
             concat('->' + group_1,
                    '->' + group_1,
                    '->' + group_3,
                    '->' + group_4
                   ) as groups
      from <table> t
     )

Note: This uses both concat() and + because they handle NULL values differently. concat() ignores NULL values, but + returns a NULL value if any argument is NULL.

Upvotes: 1

Related Questions