CODEWITHSUNDEEP
sqlsql-serverwindows
NoWar
NoWar

Reputation: 37633

Are there any visualization tools to realize the hierarchical style of the SQL query data as an output result?

I guess should be some visualization tools (MS SQL Server) to represent some hierarchical style of the SQL query data as an output result.

I just have some hierarchical table chain with 7 tables and I have to query 1-2nd level of it very often in order to check the bottom of this chain as well as some intermediate tables.

Any clue guys?

Thank you in advance!

P.S. It would be cool if MS SQL Management Studio could accept some plugins in its next generation... :)

Upvotes: 1

Views: 660

Answers (2)

Tim Lehner
Tim Lehner

Reputation: 15251

Brad Schulz has a pretty amazing proc (usp_DrawTree) here: http://bradsruminations.blogspot.com/2010/04/t-sql-tuesday-005-reporting.html

Here is one of his example outputs:

/*
                               ┌───────────┐  
                               │   Anne    │
                             ┌─┤ Dodsworth │ Sales Representative
                             │ │  Ext452   │
                             │ └───────────┘  
                ┌──────────┐ │
                │  Steven  │ │
              ┌─┤ Buchanan ├─┤ Sales Manager
              │ │ Ext3453  │ │
              │ └──────────┘ │
              │              │ ┌────────┐  
              │              │ │ Robert │
              │              ├─┤  King  │ Sales Representative
              │              │ │ Ext465 │
              │              │ └────────┘  
              │              │ ┌─────────┐  
              │              │ │ Michael │
              │              └─┤ Suyama  │ Sales Representative
              │                │ Ext428  │
              │                └─────────┘  
              │ ┌──────────┐  
              │ │  Laura   │
              ├─┤ Callahan │ Inside Sales Coordinator
              │ │ Ext2344  │
              │ └──────────┘  
  ┌─────────┐ │
  │ Andrew  │ │
  │ Fuller  ├─┤ Vice President, Sales
  │ Ext3457 │ │
  └─────────┘ │
              │ ┌─────────┐  
              │ │  Nancy  │
              ├─┤ Davolio │ Sales Representative
              │ │ Ext5467 │
              │ └─────────┘  
              │ ┌───────────┐  
              │ │   Janet   │
              ├─┤ Leverling │ Sales Representative
              │ │  Ext3355  │
              │ └───────────┘  
              │ ┌──────────┐  
              │ │ Margaret │
              └─┤ Peacock  │ Sales Representative
                │ Ext5176  │
                └──────────┘  
*/

Upvotes: 1

tbone
tbone

Reputation: 15473

For Oracle anyway (i got here via SQL tag), you can use lpad with the associated level to format the output (similar to an expanded folder view, deeper levels have more indentation):

SELECT LEVEL,
    LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
    last_name AS employee
   FROM employee
   START WITH employee_id = 1
   CONNECT BY PRIOR employee_id = manager_id;

Upvotes: 1

Related Questions