Please help Me hirarchy data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Please help Me hirarchy data

Author

Topic

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-26 : 07:25:59

I have a table with field name id, reff, direction, pv. here id is chiled id, reff is parent id, direction is left or right and pv contained 0 or 1.

table is

id reff direction pv
201 Root 1
601 201 L 0
1101 601 L 1
1201 1101 R 1
501 201 R 1
1001 501 L 1
1901 1001 L 1
2001 1901 L 1
2301 2001 L 1
2401 2301 L 0
2501 2401 L 1
2601 2401 R 1
2201 2001 R 1
1801 1001 R 1
901 501 R 1
1601 901 L 1
1701 901 R 1

I make a query below

declare @nodeid int = 201;
with cte as (
Select id, reff, direction, pv, lnode= null, rnode = null
From tree
Where id = @nodeid
Union All
Select t.id, t.reff, isnull(cte.direction,t.direction), t.pv,
lnode = CASE WHEN isnull(cte.direction,t.direction) = 'L' and t.pv = 1 THEN 1 ELSE 0 END,
rnode = CASE WHEN isnull(cte.direction,t.direction) = 'R' and t.pv = 1 THEN 1 ELSE 0 END
From tree t
Inner Join cte
On t.reff = cte.id
) select @nodeid id, SUM(lnode) LeftNodes, SUM(rnode) RightNodes from cte

but its not work properly plz help me,

Dilip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 07:43:55

whats your requirement. show your sample output for the data above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-26 : 07:47:59

I need count left and right nodes of root where pv=1

quote:
Originally posted by visakh16

whats your requirement. show your sample output for the data above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dilip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 08:34:09

you mean for each id traversing down?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-27 : 00:59:16

yes, plz give me a better solution

quote:
Originally posted by visakh16

you mean for each id traversing down?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dilip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 01:05:32

quote:
Originally posted by Dileep41011

yes, plz give me a better solution
quote:
Originally posted by visakh16

you mean for each id traversing down?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dilip

what does that mean?
First have the patience to answer the questions asked. Or show us required output out of above sample data
Unless you help us with required details i dont think anybody will be able to help you much.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-27 : 01:51:31

id---------------reff---------------direction------pv
201--------------Root---------------Root------------1
601--------------201----------------L---------------0
1101-------------601----------------L---------------1
1201-------------1101---------------R---------------1
501--------------201----------------R---------------1
1001-------------501----------------L---------------1
1901-------------1001---------------L---------------1
2001-------------1901---------------L---------------1
2301-------------2001---------------L---------------1
2401-------------2301---------------L---------------0
2501-------------2401---------------L---------------1
2601-------------2401---------------R---------------1
2201-------------2001---------------R---------------1
1801-------------1001---------------R---------------1
901--------------501----------------R---------------1
1601-------------901----------------L---------------1
1701-------------901----------------R---------------1

if I pass root ID 201 then

here total left node = 3
total Right Node = 13

here node 601 (Left) PV is 0 and
Node 2401 (Right) PV is 0

I want count left and right node of 201 where (chiled node pv =1)

Final output will be

total left node = 2
total right node = 12

Dilip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 05:00:38

something like


declare @id int
set @id=201
;WIth CTE(id,reff,direction,pv,path)
as
(
select id,reff,direction,pv,cast(id as varchar(max))
from table
where reff=@id
union all
select t.id,t.reff,t.direction,t.pv,c.path + ',' + cast(t.id as varchar(max))
from cte c
join table t
on t.reff=c.id
)

select sum(case when direction ='L' then 1 else 0 end) as LeftCount,
sum(case when direction ='R' then 1 else 0 end) as RightCount
from cte
where pv=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-27 : 05:13:39

its produce left node 7 and right node 8 while answer will be 2 and 13

quote:
Originally posted by visakh16

something like

declare @id int
set @id=201
;WIth CTE(id,reff,direction,pv,path)
as
(
select id,reff,direction,pv,cast(id as varchar(max))
from table
where reff=@id
union all
select t.id,t.reff,t.direction,t.pv,c.path + ',' + cast(t.id as varchar(max))
from cte c
join table t
on t.reff=c.id
)

select sum(case when direction ='L' then 1 else 0 end) as LeftCount,
sum(case when direction ='R' then 1 else 0 end) as RightCount
from cte
where pv=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dilip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 06:21:06

can you explain how you got 2 and 13?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-27 : 07:15:15

Plz open this link images.

https://picasaweb.google.com/114877839885608479168/September272011#5656996101066437410

[quote]Originally posted by visakh16

can you explain how you got 2 and 13?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[url][/url]

Dilip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 07:38:29

i cant view that link here. would you mind explaining the logic?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-27 : 07:52:53

here root 201 have 3 left chiled nodes and 14 right chiled nodes, and 1 chiled nodes in left has PV values 0, and 1 right nodes of root 201 has pv value 0, all others chiled nodes pv values is 1. I want count total left and right nodes of root 201 where pv values is 1, After this logic total left nodes of root 201 will be 2 and right nodes will be 12.

quote:
Originally posted by visakh16

i cant view that link here. would you mind explaining the logic?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dilip

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-27 : 13:51:31

id---------------reff---------------direction------pv
201--------------Root---------------Root------------1
601--------------201----------------L---------------0
1101-------------601----------------L---------------1
1201-------------1101---------------R---------------1
501--------------201----------------R---------------1
1001-------------501----------------L---------------1
1901-------------1001---------------L---------------1
2001-------------1901---------------L---------------1
2301-------------2001---------------L---------------1
2401-------------2301---------------L---------------0
2501-------------2401---------------L---------------1
2601-------------2401---------------R---------------1
2201-------------2001---------------R---------------1
1801-------------1001---------------R---------------1
901--------------501----------------R---------------1
1601-------------901----------------L---------------1
1701-------------901----------------R---------------1

here root 201 have 3 left chiled nodes and 14 right chiled nodes, and 1 chiled nodes in left has PV values 0, and 1 right nodes of root 201 has pv value 0, all others chiled nodes pv values is 1. I want count total left and right nodes of root 201 where pv values is 1, After this logic total left nodes of root 201 will be 2 and right nodes will be 12.

Is left-right node based on direction column? Your sample data indicates that there are 8 left nodes for the rood 201. It is a bit confusing .....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 23:16:40

quote:
Originally posted by namman

id---------------reff---------------direction------pv
201--------------Root---------------Root------------1
601--------------201----------------L---------------0
1101-------------601----------------L---------------1
1201-------------1101---------------R---------------1
501--------------201----------------R---------------1
1001-------------501----------------L---------------1
1901-------------1001---------------L---------------1
2001-------------1901---------------L---------------1
2301-------------2001---------------L---------------1
2401-------------2301---------------L---------------0
2501-------------2401---------------L---------------1
2601-------------2401---------------R---------------1
2201-------------2001---------------R---------------1
1801-------------1001---------------R---------------1
901--------------501----------------R---------------1
1601-------------901----------------L---------------1
1701-------------901----------------R---------------1

here root 201 have 3 left chiled nodes and 14 right chiled nodes, and 1 chiled nodes in left has PV values 0, and 1 right nodes of root 201 has pv value 0, all others chiled nodes pv values is 1. I want count total left and right nodes of root 201 where pv values is 1, After this logic total left nodes of root 201 will be 2 and right nodes will be 12.

Is left-right node based on direction column? Your sample data indicates that there are 8 left nodes for the rood 201. It is a bit confusing .....

yeah..same confusion here to

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-28 : 00:58:40

Yes, left-right node based on direction column.

quote:
Originally posted by visakh16

quote:
Originally posted by namman

id---------------reff---------------direction------pv
201--------------Root---------------Root------------1
601--------------201----------------L---------------0
1101-------------601----------------L---------------1
1201-------------1101---------------R---------------1
501--------------201----------------R---------------1
1001-------------501----------------L---------------1
1901-------------1001---------------L---------------1
2001-------------1901---------------L---------------1
2301-------------2001---------------L---------------1
2401-------------2301---------------L---------------0
2501-------------2401---------------L---------------1
2601-------------2401---------------R---------------1
2201-------------2001---------------R---------------1
1801-------------1001---------------R---------------1
901--------------501----------------R---------------1
1601-------------901----------------L---------------1
1701-------------901----------------R---------------1

here root 201 have 3 left chiled nodes and 14 right chiled nodes, and 1 chiled nodes in left has PV values 0, and 1 right nodes of root 201 has pv value 0, all others chiled nodes pv values is 1. I want count total left and right nodes of root 201 where pv values is 1, After this logic total left nodes of root 201 will be 2 and right nodes will be 12.

Is left-right node based on direction column? Your sample data indicates that there are 8 left nodes for the rood 201. It is a bit confusing .....

yeah..same confusion here to

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dilip

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-28 : 01:13:38

Yes, left-right node based on direction column.

quote:
Originally posted by namman

id---------------reff---------------direction------pv
201--------------Root---------------Root------------1
601--------------201----------------L---------------0
1101-------------601----------------L---------------1
1201-------------1101---------------R---------------1
501--------------201----------------R---------------1
1001-------------501----------------L---------------1
1901-------------1001---------------L---------------1
2001-------------1901---------------L---------------1
2301-------------2001---------------L---------------1
2401-------------2301---------------L---------------0
2501-------------2401---------------L---------------1
2601-------------2401---------------R---------------1
2201-------------2001---------------R---------------1
1801-------------1001---------------R---------------1
901--------------501----------------R---------------1
1601-------------901----------------L---------------1
1701-------------901----------------R---------------1

here root 201 have 3 left chiled nodes and 14 right chiled nodes, and 1 chiled nodes in left has PV values 0, and 1 right nodes of root 201 has pv value 0, all others chiled nodes pv values is 1. I want count total left and right nodes of root 201 where pv values is 1, After this logic total left nodes of root 201 will be 2 and right nodes will be 12.

Is left-right node based on direction column? Your sample data indicates that there are 8 left nodes for the rood 201. It is a bit confusing .....

Dilip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 01:38:23

then how will it become 3? there are 7 left nodes with pv=1 rite?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-28 : 02:12:00

plz see this link images
https://picasaweb.google.com/114877839885608479168/September272011#5656996101066437410

quote:
Originally posted by visakh16

then how will it become 3? there are 7 left nodes with pv=1 rite?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dilip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 02:29:27

i'm getting page not found error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dileep41011
Starting Member

11 Posts

Posted - 2011-09-28 : 04:54:39

I am sending another link here, plz review

http://imageshare.web.id/images/f3eepvszdt7caeto.png

quote:
Originally posted by visakh16

i'm getting page not found error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dilip

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources