SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Complex hierarchy: how to build?


Complex hierarchy: how to build?

Author
Message
fasttrack2
fasttrack2
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 179
(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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26139 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
fasttrack2
fasttrack2
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 179
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
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26139 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
fasttrack2
fasttrack2
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 179
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86249 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
fasttrack2
fasttrack2
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 179
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86249 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Dennis Post
Dennis Post
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 547
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.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
fasttrack2
fasttrack2
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 179
Many thanks guys,

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

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search