Good Day
I slept over this and i came back with this and it solved the Problem
/****** Object: StoredProcedure [dbo].[sp_Traverse_Tree_Special] Script Date: 02/16/2010 22:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Traverse_Tree_Special] @curr int
with recompile
AS
set nocount on
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes]'))
drop table [#Nodes]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes_FINAL]'))
drop table [#Nodes_FINAL]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#SemiFinal]'))
drop table [#SemiFinal]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Surt1]'))
drop table [#Surt1]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Children]'))
drop table [#Children]
Create table [#Nodes]
(id int IDENTITY(0,1),
NodeID int,
parent int,
[Description] varchar(128),
refParent int
)
Create table [#Nodes_FINAL]
(id int IDENTITY(0,1),
id2 int ,
NodeID int,
parent int,
[Description] varchar(128),
refParent int
)
INSERT INTO #Nodes
(NodeID, Parent, [Description])
select distinct n.ID NodeID, n.Parent, nTyp.Descr [Description]
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr in ('Compulsory','One of')
where n.curr = @curr
union
select distinct n.ID NodeID, n.Parent, m.Descr [Description] --, n.Type, n.Curr, m.Descr
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr = 'Subject'
inner join tbl_modl m on m.id = n.modl
where n.curr = @curr
select distinct nP.id as [id], nP.NodeID, nP.parent, nP.Description, nRef.ID refParent
into #nodes2 from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent
order by refParent,nP.Description
WITH CTENodes AS
(
SELECT
ID,
NODEID,
PARENT,
DESCRIPTION,
REFPARENT,
CAST(ROW_NUMBER() OVER(ORDER BY id ) AS VARCHAR(MAX)) NodePath
FROM #nodes2
WHERE REFPARENT is null
UNION ALL
SELECT
c.ID,
c.NODEID,
c.PARENT,
c.DESCRIPTION,
c.REFPARENT,
NodePath + '.' + CAST(ROW_NUMBER() OVER(ORDER BY C.id ) AS VARCHAR(MAX)) NodePath
FROM CTENodes AS P
JOIN #nodes2 AS C
ON C.REFPARENT = P.id
)
SELECT ID,NODEID, PARENT, DESCRIPTION, REFPARENT
into #SemiFinal
FROM CTENodes
ORDER BY NodePath,ID,REFPARENT
--These are all Records that inherit from the Root
select distinct s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Surt1 from #SemiFinal s1
inner join #SemiFinal s2
on s1.id = s2.id
where s1.RefParent = 0
and s1.id != s1.Refparent
and s1.Parent in (select Nodeid from #semifinal where Parent is null and ID = 0)
--Find Records that are Children
select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Children from #SemiFinal s1
where Refparent <> 0
--Get the Record that does not have Children
--select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent from #Surt1 s1
--where s1.id not in (select refparent from #Children)
--delete those that have Children from the #surf1 table
delete #Surt1
from #Surt1 s1
where s1.id in (select refparent from #Children)
--Then Union the Results the way you want them
--First Union the Root
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID , nodeid , parent, [description], RefParent from #SemiFinal
where Refparent is null
--Follow with the Children
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID , nodeid , parent, [description], RefParent from #Surt1
--Follow with the Normal
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID, nodeid , parent, [description], RefParent from #SemiFinal
where Refparent is not null
and ID not in (select ID from #Surt1)
select distinct ID, nodeid , parent, [description] from #Nodes_FINAL
order by nP.id , nodeid
Thank you
Thank you for posting at Dotnetfunda
[Administrator]
Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator