Databases
StackOverflow2013
Postsdbo
POHow to visualize only the descendants, ancestors, and self of a node in a hierarchy?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to visualize only the descendants, ancestors, and self of a node in a hierarchy?
    primarykey
    data
    text
    <p>In another <a href="https://stackoverflow.com/questions/7111467/what-tool-generates-diagrams-from-sql-server-hierarchical-data">question</a> I asked about visualizing hierarchical data stored in a table in an SQL Server database. I found a way to visualize the whole hierarchy using <a href="http://www.graphviz.org/" rel="nofollow noreferrer">GraphViz</a>, with some plumbing in T-SQL and Powershell.</p> <p>I want to use a visualization like this to debug an application that uses similar data. Visualizing everything is fine for the small example hierarchy. But in a hierarchy of thousands, this is overwhelming.</p> <p>When I debug my application, I usually look at only a small set of nodes related to a given node. Currently, the only related nodes that matter to me for a given node are the descendants and the ancestors, as well as the node itself.</p> <p>So, I want a way to visualize only the nodes in the hierarchy that are the descendants, ancestors, or self of a given node.</p> <p>The following statements create the example database and table as in the linked question.</p> <pre><code>CREATE DATABASE HierarchyTest; GO USE HierarchyTest; GO CREATE TABLE NodeHierarchy ( PK_NodeID INT NOT NULL CONSTRAINT PK_NodeHierarchy PRIMARY KEY, FK_ParentNodeID INT NULL CONSTRAINT FK_NodeHierarchy_NodeHierarchy FOREIGN KEY REFERENCES NodeHierarchy(PK_NodeID), Name NVARCHAR(255) NOT NULL ); </code></pre> <p>The following statement populates the table with a modified version of the hierarchy of countries, cities, and venues. The United Kingdom is now the root node, and there are more nodes to represent famous English venues.</p> <pre><code>INSERT INTO NodeHierarchy(PK_NodeID, FK_ParentNodeID, Name) VALUES (1, 18, N'Scotland'), (2, 1, N'Glasgow'), (3, 1, N'Edinburgh'), (4, 1, N'St Andrews'), (5, 2, N'The Barrowlands'), (6, 2, N'The Cathouse'), (7, 2, N'Carling Academy'), (8, 2, N'SECC'), (9, 2, N'King Tut''s Wah-Wah Hut'), (10, 3, N'Henry''s Cellar Bar'), (11, 3, N'The Bongo Club'), (12, 3, N'Sneaky Pete''s'), (13, 3, N'The Picture House'), (14, 3, N'Potterrow'), (15, 4, N'Aikman''s'), (16, 4, N'The Union'), (17, 4, N'Castle Sands'), (18, NULL, N'United Kingdom'), (19, 15, N'Upstairs'), (20, 15, N'Downstairs'), (21, 16, N'Venue 1'), (22, 16, N'Venue 2'), (23, 18, N'England'), (24, 23, N'Manchester'), (25, 24, N'Apollo Theatre'), (26, 18, N'Liverpool'), (27, 26, N'Cavern Club'); </code></pre> <p>The following image is the output of Powershell script <code>generate-graph.ps1</code> listed in the linked question. If the Stack Overflow reduced-size version looks ugly, look at the <a href="https://i.stack.imgur.com/aoTB7.png" rel="nofollow noreferrer">full-size image</a>.</p> <p><img src="https://i.stack.imgur.com/aoTB7.png" alt="Visualization of entire hierarchy generated by GraphViz"></p> <p>I want to see only how St Andrews' descendants and ancestors relate to it. The diagram contains a lot of information irrelevant to these relationships, and so is harder to read. When I scale my hierarchy up to thousands of nodes covering cities and venues globally, a full visualization becomes almost useless.</p> <p>In <a href="http://freemind.sourceforge.net/wiki/index.php/Main_Page" rel="nofollow noreferrer">Freemind</a> I drew a crude diagram of what I would like to see instead:</p> <p><img src="https://i.stack.imgur.com/NCezP.png" alt="Hand-constructed diagram of descendants, anscestors, and self of St Andrews"></p> <p>How do I extract only the data that is relevant to St Andrews so I can give it to GraphViz?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USIain Samuel McLean Elder
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to visualize only the descendants, ancestors, and self of a node in a hierarchy?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to visualize only the descendants, ancestors, and self of a node in a hierarchy?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to visualize only the descendants, ancestors, and self of a node in a hierarchy?
      1. USM.R.
      1. VTFavorite
    1. COThis is a Jeopardy-style question. I already have my own solution, but I want to share the problem that it solves too. I will post my own solution soon.
      singulars
      1. POHow to visualize only the descendants, ancestors, and self of a node in a hierarchy?
      1. USIain Samuel McLean Elder
    2. COare you using sql server 2008?
      singulars
      1. POHow to visualize only the descendants, ancestors, and self of a node in a hierarchy?
      1. USM.R.
    3. COI am using SQL Server 2008. I haven't forgotten about posting my solution, by the way; I've just been busy!
      singulars
      1. POHow to visualize only the descendants, ancestors, and self of a node in a hierarchy?
      1. USIain Samuel McLean Elder
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload