Tree-type data processing example in SQL Server 2005-2

server| Sample | data

-- =====================================================
--application example of direct query
-- =====================================================

--1. FullName for each leaf node
with STB ([Id],[fullname],[pid],[flag])
as (
     SELECT [Id],cast (RTRIM ([name]) as nvarchar (4000)), [pid],1
    from [TB] A
     WHERE not EXISTS (
        SELECT 1 from [TB]
         WHERE [Pid]=a.[id])
    UNION all
    SELECT A.[id],rtrim (b.[ Name] + '/' +a.[fullname],b.[pid],a.flag+1
    from STB A,[TB] B
    WHERE A.[pid] =b.[id])
SELECT [Id],[fullname] from STB A
WHERE not EXISTS (
    SELECT * from STB
 & nbsp;  WHERE [Id]=a.[id]
        and Flag>a.flag)
order by [ID]
Go

--2. FullName for each node
with STB ([Id],[fullname],[pid],[flag])
as (
     SELECT [Id],cast (RTRIM ([name]) as nvarchar (4000)), [pid],1
    from [TB]
     UNION all
    SELECT A.[id],rtrim (b.[name]) + '/' +a.[fullname],b.[pid],a.flag+1
     from STB A,[TB] B
    where A.[pid]=b.[id])
SELECT [Id],[fullname] from STB A
WHERE Not EXISTS (
    SELECT * from STB
    WHERE [Id]=a.[id]
         and Flag>a.flag)
order by [id]
Go

--3. Tree display Data
with STB ([Id],[level],[sid])
as (
    SELECT [id],1, CAST (Right (10000+[id],4) as varchar (8000))
    from [TB]
    WHERE [pid]=0
    UNION all
    SELECT a.[id],b.[level]+1,b.sid+right (10000+a.[id],4)
     from [TB] a,stb B
    WHERE a.[pid]=b.[id])
SELECT N ' | ' +replicate ('-', b.[level]*4) +a.name
from [TB] a,stb B
WHERE A.[id]=b.[id]
ORDER by b.sid   
Go

--4. Check for nonstandard data
with CHKTB ([Id],[pid],[level],[path],[flag])
as (
     SELECT [id],[pid],1,
        CAST ([id] as varchar (8000)),
         case When [id]=[pid] THEN 1 ELSE 0 "
    from [TB]
 & nbsp;  UNION all
    SELECT a.[id],b.[pid],b.[level]+1,
         CAST (b.[path]+ ' > ' +rtrim (a.[id]) as varchar (8000)),
        Case when A.[id]=b.[pid] THEN 1 ELSE 0 "
    from [TB] A,CHKTB B
    WHERE a.[pid]=b . [ID]
        and B.[flag]=0)
SELECT * from CHKTB
WHERE [flag]=1
Order by [path]   
Go

--5. Query node for all child nodes
with SUMTB ([Id],[level])
as (
    SELECT [pid],1
    from [TB] A
    WHERE [pid]<>0
    UNION all
    SELECT a.[pid],b.[level]+1
    from [TB] A,SUMTB B
    WHERE A. [Id]=b.[id]
        and a.[pid]<>0)
SELECT a.[id],childcounts= COUNT (B.[id])
from [TB] A
    left JOIN sumtb b
        on A.[id]=b.[id]
GROUP by a.[id]
Go

--6. Query all parent nodes of a node
With SUMTB ([id],[level],[parentcounts])
As (
SELECT [id],1,0
From [TB] A
WHERE [pid]=0
UNION All
SELECT a.[id],b.[level]+1,b.[parentcounts]+1
From [TB] A,SUMTB B
WHERE A.[pid]=b.[id])
SELECT * from SUMTB
Order BY [ID]
Go