fasttrack2 (9/9/2013)

---

(SQL SERVER 2008 R2)

Hi guys,

I need some help on how to retrieve Hierarchy in a table.

In this case, the example is more complex than the the traditional one: I mean, the hirarchy stops and restarts when along the chain a special value type is encountered:

To make an example:

The Chain:

Px: Parents

Cx: Childs

P1 -- C1 -- C2 -- P2 -- C3 -- C4 --C5 -- C6 -- P3 -- C7

Hi need to create a hierarchy like:

P1 C1 (P1 is parent of C1, C1 is child of P1)

P1 C2 (P1 is parent of C2, C2 is child of P1)

P2 C3 (P2 is parent of C3, C3 is child of P2)

P2 C4 .... and so on

P2 C5

P2 C6

P3 C7

It means that every time a Px is encountered, the hierarchy must be stopped and started with a new hierarchy.

The above is only an example. The chain is more complex (eg: several chains, the number of <Px> for every chain is unknown, and the number of child <CX> between <Px> is unknown)

Do you know how to build it?

Many thanks

P1 -- C1 -- C2 -- P2 -- C3 -- C4 --C5 -- C6 -- P3 -- C7