Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Complex hierarchy: how to build? Expand / Collapse
Author
Message
Posted Monday, September 9, 2013 10:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 17, Visits: 139
(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
Post #1492872
Posted Monday, September 9, 2013 1:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 13,073, Visits: 11,912
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 Moden's 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)
Post #1492901
Posted Monday, September 9, 2013 1:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 17, Visits: 139
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.
Post #1492904
Posted Monday, September 9, 2013 1:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 13,073, Visits: 11,912
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 Moden's 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)
Post #1492910
Posted Monday, September 9, 2013 2:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 17, Visits: 139
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
Post #1492914
Posted Monday, September 9, 2013 5:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 36,743, Visits: 31,192
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1492957
Posted Tuesday, September 10, 2013 2:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 17, Visits: 139
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
Post #1493038
Posted Tuesday, September 10, 2013 5:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 36,743, Visits: 31,192
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1493126
Posted Tuesday, September 10, 2013 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, 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
Post #1493178
Posted Tuesday, September 10, 2013 4:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 17, Visits: 139
Many thanks guys,

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

Thanks
Post #1493391
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse