Microsoft SQL Recursive Common Table Expression CTE

Microsoft SQL Recursive Common Table Expression CTE

-- Recursive statements can be used in conjunction with CTE
-- This one will use wildcard queries, dense rank function, and joins

--original table
select *
from groupings;

--recursive CTE
with groupersCTE (groups, subgroups, [grouping])
as
(
select g2.subgroup,g1.subgroup,DENSE_RANK() over (order by len(g2.subgroup) ) as counters
from dbo.groupings g1,dbo.groupings g2
where g1.subgroup like g2.subgroup+'%'
and len(g1.subgroup)>len(g2.subgroup)
)
select groups, subgroups, [grouping]+1 as [grouping]
from groupersCTE;