Create parent child from ordered list

Transact-SQL
1

Needing to convert a flat structure to a parent child table. Have large sets of data with a sequential id and a level column. The top level contains multiple second levels. The last level is not set. The data looks like the following:

id group level name
1 G1 1 A1
2 G1 2 A2
3 G1 2 A3
4 G1 3 A4
5 G1 4 A5
6 G1 2 A2
7 G1 3 A3
8 G1 3 A5
9 G1 2 A6

So 1 will be the parent of all the twos under it and a new section of ones can exist in G1. Same at every level. So I want to fill in the parent id for each level. So all the twos would be id 1. The id 4 ouls have 3 as it parent. Id 7 would have 6 as its parent. So need complete details on a recursive way to populate the parent or what is the fastest was with out going through line by line..

2

The id 4 ouls have 3 as it parent.

There's a reason that you're not getting an answer to your question; namely, nobody can understand the requirements. What would the output look like, given the data set that you have provided?

3

ouls is will sorry for the typo. There are multiple level 1's for G1 as well with children.

id group level name Parent
1 G1 1 A1 NULL
2 G1 2 A2 1
3 G1 2 A3 1
4 G1 3 A4 3
5 G1 4 A5 4
6 G1 2 A2 1
7 G1 3 A3 6
8 G1 3 A5 6
9 G1 2 A6 1

4

See? All ya had to do was to clarify...[code]---------------------------------------------------
-- This section is what YOU should be supplying...

declare @tbl table (
id int,
[group] varchar(5),
level int,
name varchar(5)
)

insert into @tbl
values
(1, 'G1', 1, 'A1'),
(2, 'G1', 2, 'A2'),
(3, 'G1', 2, 'A3'),
(4, 'G1', 3, 'A4'),
(5, 'G1', 4, 'A5'),
(6, 'G1', 2, 'A2'),
(7, 'G1', 3, 'A3'),
(8, 'G1', 3, 'A5'),
(9, 'G1', 2, 'A6')

--/**/select * from @tbl

--- This section is what we MIGHT be supplying...
--- (Make it easy for us to help you!)

select
child.*, parent.id as parent
from
@tbl child
outer apply
(
select max(p.id) id
from @tbl p
where
p.[group] = child.[group]
and p.level = child.level - 1
and p.id < child.id
) parent[/code]

1 Like