SQL Server查询或工具以显示分层数据 [英] SQL Server Query or Tool to show Hierarchical Data

问题描述

我们有一个通用的组织表结构，认为它是树或金字塔层次结构.我们基本上有多个要显示的树".代表一家公司，代表另一家ETC.

We have a general organizational table structure, think of it s a Tree or Pyramid Hierarchy. We basically have multiple "trees" we want to show. On for one company, one for another ETC.

有人知道显示此数据的好方法吗? SQL Query会很好，怀疑是否可能，但是我不会反对使用某些OTS工具(最好是免费的).我也想避免某些类型的报告.我不需要实际的解决方案，只需要知道(如果可能).因此，如果您说SQL，那么如果您能给我举2个显示根事假的表格示例，我会很高兴的.

Does anyone know of a good way to display this data? SQL Query would be nice, doubt it would be possible, but I wouldn't be against using some OTS tool (preferably free). I would also like to avoid some type of report. I don't need an actual solution just need to know if its possible. So if you say SQL if you can give me a 2 table example of showing a root a leave I would be happy.

结构很普通

每个表都通过代理键CompanyID，CompanyGroupID等链接.

And each table is linked through a surrogate key CompanyID, CompanyGroupID, etc.

关于我们如何显示/查询此数据的任何建议?不得已的方法是编写一个快速的C#Windows应用程序...

Any suggestions on ways we could display/query for this data? Last resort is to write a quick C# Windows Application...

我们希望以树形形式看到它:

We would like to see it in tree form:

--                      1-Company
--                     /        \
--             CompanyGroupA   CompanyGroupB
--            /       \              \
--  CompanyStoreA1 CompanyStoreA1 CompanyStoreB
--    /      \            /    \
--Employee   A            B     C   

为使大众满意，下面是一个示例查询脚本，用于填充查询.

In attempt to please the masses here is an example test script to populate the query.

DECLARE @Company table (id int, name varchar(40) )
INSERT @Company VALUES (1,'Living Things' )  
INSERT @Company VALUES (2,'Boring Company' )  


DECLARE @CompanyGroup table (id int, name varchar(40), CompanyID int)
INSERT @CompanyGroup VALUES (1,'Pets',1 ) 
INSERT @CompanyGroup VALUES (2,'Humans',1 ) 
INSERT @CompanyGroup VALUES (3,'Electronics',2 ) 
INSERT @CompanyGroup VALUES (4,'Food',2 ) 


DECLARE @CompanyStore table (id int, name varchar(40), CompanyGroupID int)
INSERT @CompanyStore VALUES (1,'PetsStoreA',1 ) 
INSERT @CompanyStore VALUES (2,'PetsStoreB',1 ) 
INSERT @CompanyStore VALUES (3,'PetsStoreC',1 ) 
INSERT @CompanyStore VALUES (4,'PetsStoreD', 1) 
INSERT @CompanyStore VALUES (5,'HumansStore',2 ) 
INSERT @CompanyStore VALUES (6,'FoodStore',3 ) 

最终的解决方案非常棒，我修改了usp_DrawTree以接受varchar vs ints，因为我必须使查询ID唯一.然后，我只是全部选择/合并并建立了父子关系.

The final solution was pretty awesome I modified the usp_DrawTree to accept varchar vs ints because i had to make my query ids unique. I then just did a select/union all and built the parent child relationship.

select * into #TreeData from (
  select ID='C' + cast(id as varchar(10)),
       ParentID=null,
       DataForBox=name + '(' + cast(id as varchar(10)) + ')',
       ExtraInfo='', 
       SortColumn=name
  from Company c 
 )
union all (
  select ID='CG' + cast(id as varchar(10)),
       ParentID=cg.CompanyID ,
       DataForBox=name + '(' + cast(id as varchar(10)) + ')',
       ExtraInfo='', 
       SortColumn=name
  from CompanyGroup cg join Company c on c.ID=cg.CompanyID 
  ) 
//union all rest of hierarchy
)

推荐答案

布拉德·舒尔茨(Brad Schulz)以

Brad Schulz to the rescue in the form of usp_DrawTree.

这篇关于SQL Server查询或工具以显示分层数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！