Complex hierarchy: how to build?

Question

Complex hierarchy: how to build?

fasttrack2

Mr or Mrs. 500

Points: 537
More actions
September 9, 2013 at 10:59 am

#282164

(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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

There is nowhere near enough information for anybody to be able to tell you "how to build it". Is this a brand new thing and you are trying to design the table to hold this information or is does this already exist and you are trying to figure out how to retrieve the information?

It almost sounds like you have multiple hierarchies? Meaning you have a number of "root nodes"?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

fasttrack2 Mr or Mrs. 500 Points: 537 More actions · Answer 2

Hi and many thanks,

Unfortunately the system is already done and therefore no modifications are allowed.

Yes, the example is only just to show the complexity, but the real system is very articulated with the chance to make chains (hierachies) connected to each other.

So there is not a schema predifined.

I understand that to include a real example should be better but:

1) I cannot disclose information

2) The Hierarchy are so articulate that are difficult to present as example.

Kind regards.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

So just to reiterate, you have a system but you can't share the design. You need to build a hierarchy but you can't share or create any sample data. Even if you could share, the system is entirely too complicated to put together enough details to come up with some sample data.

However, you need to get data out of this system and you can't do it without some help because the system is so complicated you don't know how to go about it.

Sounds to me there is about one choice left, hire a consultant have them sign a nondisclosure agreement.

I don't mean to sound rude but honestly there is nothing we can do here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

fasttrack2 Mr or Mrs. 500 Points: 537 More actions · Answer 4

Hi and many thanks.

I cannot extract data because are client ones and cannot be exctracted.

To provide more information I need to prepare something of similar that may represent the data.

Thanks

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 5

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

Actually, that's a pretty good explanation... or at least I get it.

In the following....

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

Is that a pretty good representation of the chain? For example, are the nodes actually separated by a space, two dashes, and a space and that chain is actually string data in a single column?

Also, the chain you show only has two levels of data. Does the chain actually contain any more levels than that? If so, could you provide a similar chain to show the 3 or more levels?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fasttrack2 Mr or Mrs. 500 Points: 537 More actions · Answer 6

Hi and many thanks.

the only <fortunate> thing is that we have only 2 objects Px and Cx.

Px and Cx are easly identified because they have a <column> that says if they are Px and Cx.

Px and Cx are longer names: they are string but currently have not seen <dashes> or <spaces> or something like that could generate issues.

Just to expalin more about the complexity: we have several chain taht may interconnect to each other,

Thanks (and sorry not presenting an example: but I need to rebuild the the data).

Meanwhile if you could provide some help will be very appreciated.

Many thanks

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 7

I just need to know exactly what the string has in it format wise and an accurate but made up example would do fine. with the understanding that x can be of any length, is your example of P1 -- C1 -- C2 -- P2 -- C3 -- C4 --C5 -- C6 -- P3 -- C7 and accurate example of the string including the dashes and spaces between the nodes? If not, I need to know. This actually isn't a difficult problem to solve but I need to know what the actual format of these strings is like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 8

Looks like a job for a recursive query.

Make up some sample data so we can help you out. (Create table + inserts + clean up)

Or you could check out the great SQLServerCentral articles and blogs on the subject.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

fasttrack2 Mr or Mrs. 500 Points: 537 More actions · Answer 9

Many thanks guys,

I will access the database server to provide you with more information.

Thanks

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 10

Dennis Post (9/10/2013)
Looks like a job for a recursive query.
Make up some sample data so we can help you out. (Create table + inserts + clean up)
Or you could check out the great SQLServerCentral articles and blogs on the subject.

I believe that it's actually a job for a simple splitter and a tiny bit of pair matching. If we can get a more clear example from the OP of even just one of these "hierarchical paths", this will be a simple job to convert to an Adjacency List "Orchard". From there, the sky is the limit because we can then slave it into a multi-tree orchard. The "Orchard" here is very similar a task I did in the past where I had to organize such data into an "Orchard" of hundreds of thousands of "Trees" all "living" on common "ground". Once it was converted to an Adjacency List, the next step was to convert it to Nested Sets and that's a whole lot easier and faster to do than it's ever been before (and, yes, it then uses an rCTE as part of the process). Please see the following article on that.

http://www.sqlservercentral.com/articles/Hierarchy/94040/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 11

Thanks for the link Jeff.

:blush:

Bit beyond me at the moment though.

I failed the "Who This Article Is For" section.

Never heard of Adjacency List or Nested Sets.

If / when I get involved with hierarchies, I know who's name to google. 🙂

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort