Extract Tree structure data

Transact-SQL
#1

I want to extract data from a table .... i want to extract data for the mentioned three scenarios

45424920_2064129170319710_7385369855165202432_n

Here is the sample table structure and data.
Create table Tbl1
(Parent varchar(50),
ParentRev varchar(50),
Child varchar(50),
childrev varchar(50),
TNum varchar(50))

insert into tbl1('Parent1','001','child1','001','104');
insert into tbl1('Parent1','001','child1','001','104');
insert into tbl1('Parent1','001','child1','002','104');
insert into tbl1('Parent1','001','child2','002','104');
insert into tbl1('Parent1','001','child1','001','104');
insert into tbl1('Parent1','001','child1','002','104');
insert into tbl1('Parent1','001','child3','003','105');
insert into tbl1('Parent1','002','child4','001','106');
insert into tbl1('Parent2','001','child5','001','102');
insert into tbl1('Parent2','001','child6','004','102');
insert into tbl1('Parent2','001','child7','005','110');

#2

And.....what do you want to extract?

#3

hi
please provide sample data
OR
data from the table

example

drop table data
go

create table data
(
name varchar(100),
age int
)

insert into data select 'harish',40
insert into data select 'pam',40

#4

https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server

#5

Here is the sample table structure and data.
Create table Tbl1
(Parent varchar(50),
ParentRev varchar(50),
Child varchar(50),
childrev varchar(50),
TNum varchar(50))

insert into tbl1('Parent1','001','child1','001','104');
insert into tbl1('Parent1','001','child1','001','104');
insert into tbl1('Parent1','001','child1','002','104');
insert into tbl1('Parent1','001','child2','002','104');
insert into tbl1('Parent1','001','child1','001','104');
insert into tbl1('Parent1','001','child1','002','104');
insert into tbl1('Parent1','001','child3','003','105');
insert into tbl1('Parent1','002','child4','001','106');
insert into tbl1('Parent2','001','child5','001','102');
insert into tbl1('Parent2','001','child6','004','102');
insert into tbl1('Parent2','001','child7','005','110');

#6

hi

i was able to do it like this
is this okay ????

i hard coded ParentRev column ...

drop create data 
use tempdb 
go 


drop  table Tbl1
go 


Create table Tbl1
(Parent varchar(50),
ParentRev varchar(50),
Child varchar(50),
childrev varchar(50),
TNum varchar(50))

insert into tbl1 values ('Parent1','001','child1','001','104');
insert into tbl1 values ('Parent1','001','child1','001','104');
insert into tbl1 values ('Parent1','001','child1','002','104');
insert into tbl1 values ('Parent1','001','child2','002','104');
insert into tbl1 values ('Parent1','001','child1','001','104');
insert into tbl1 values ('Parent1','001','child1','002','104');
insert into tbl1 values ('Parent1','001','child3','003','105');
insert into tbl1 values ('Parent1','002','child4','001','106');
insert into tbl1 values ('Parent2','001','child5','001','102');
insert into tbl1 values ('Parent2','001','child6','004','102');
insert into tbl1 values ('Parent2','001','child7','005','110');

select * from tbl1
go
SQL 
SELECT parent, 
       'A'                                                AS ParentRev, 
       child, 
       Replace(Replace(childrev, '002', 'B'), '001', 'A') AS childrev, 
       tnum, 
       Dense_rank() 
         OVER( 
           ORDER BY child, childrev DESC, tnum )          AS drank 
FROM   tbl1 
WHERE  tnum = '104' 

go
Result

image
image.png1126×439 7.95 KB

#7

for 104 ..hardcoading is working..But we have millions of records with the similar kind. I want to extract it dynamically .

#8

You can use

group by tnum

Instead of

tnum = '104'