SQL Server以特定格式显示数据树 [英] SQL Server to show a data tree in a specific format

问题描述

我们有一个这样的表（简化版）：

项目：

  Itemid Itemname Itemfatherid 
 itemA theitemA null 
 itemB theitemB null 
 itemC theitemC itemA 
 itemD theitemD itemA 
 itemE theitemE itemC 
 itemF theitemF itemE 
 itemG theitemG itemD 
  

我们需要一个提供以下结果/格式的sql查询：更正版本）

  Col1 Col2 Col3 Col4 
 itemA itemC itemE itemF 
 itemA itemD itemG NULL 
 itemB NULL NULL NULL 
  

我们的ERP将采用这个结果并将其转换为像这样的树形控件：

  -itemA 
 -itemC 
 -itemE 
 itemF 
 -itemD 
 itemG 
 itemB 
  

树的等级不固定，所以数目列必须是动态的...

CTE有一些方法，但是我们还不能达到解决方案：S

http://www.sqlservercurry.com/2009/06/simple-family-tree-query-using。我们还需要知道树的深度（将其传递给GridControl ...），在这个例子中它将是3（它需要最大数量的父级别 - > -itemA -itemC -itemE）

解决方案

示例表

$ （ItemID varchar（100），Itemname varchar（100），Itemfatherid varchar（100））
插入so1 select

  create table so1 $ b'itemA'，'theitemA'，null union all select 
'itemB'，'theitemB'，null union all select 
'itemC'，'theitemC'，'itemA'union all select $ b item'''item'，'itemA'union all选择
'itemE'，'theitemE'，'itemC'union all select 
'itemF'，'theitemF' ，'itemE'union all select 
'itemG'，'theitemG'，'itemD'
  

查询

 如果OBJECT_ID（'tempdb ..＃tmp'）不为空drop table #tmp 
; 
 create table #tmp（
 uniqueid uniqueidentifier not null，
 level int not null，
 itemid varchar（100）null，
主键集群（uniqueid，level） 
）
; cte（level，itemid，parentid，uniqueid）作为
（
从so1中选择1，itemid，itemfatherid，NEWID（）
 
不存在的地方（select * from so1 k where k.itemfatherid = so1.itemid）
 union all 
 select cte.level + 1，t.itemid，t.itemfatherid，cte.uniqueid 
从cte 
内部连接so1 t到t.Itemid = cte.parentid 
）
插入#tmp（uniqueid，level，itemid）
 select uniqueid，level，itemid 
从cte 
选项（maxrecursion 1000） - 根据需要
; 
;用tmp作为（
 select *，newlevel = ROW_NUMBER（）over（由uniqueid order by level desc分区）
 from #tmp）
 update tmp 
 set level = newlevel 
; 
 declare @sql nvarchar（max），@columns nvarchar（max）
; 
 set @sql = CONVERT（nvarchar（max），（
）从master..spt_values 
中选择数字[data（）] 
其中type ='P'且数字在1和（从#tmp中选择MAX（level））
 order by 1 
 for xml path（'a'）））
 select @sql = stuff（replace（replace（@sql，' < / a>< a>'，']，['），'< / a>'，']'），1,3，'['）
 select @sql ='
从#tmp 
中选择'+ @sql +'
（'+ @sql +'）中的level（max（itemid））v 
 order by'+ @sql 
 exec（@sql）
  

输出

  1 2 3 4 
 itemA itemC itemE itemF 
 itemA itemD itemG NULL 
 itemB NULL NULL NULL 
  

We have a table like this (simplified version):

Items:

Itemid   Itemname    Itemfatherid
itemA    theitemA    null
itemB    theitemB    null
itemC    theitemC    itemA
itemD    theitemD    itemA
itemE    theitemE    itemC
itemF    theitemF    itemE
itemG    theitemG    itemD

We need a sql query that gives the following result/format: (Corrected version)

Col1    Col2    Col3    Col4
itemA   itemC   itemE   itemF
itemA   itemD   itemG   NULL
itemB   NULL    NULL    NULL

Our ERP would take this resul and convert it to a tree control like this:

-itemA
    -itemC
        -itemE
            itemF
    -itemD
        itemG
itemB

The level of the tree is not fixed so the number of columns must be dynamic...

There is some way with CTEs but we can't reach te solution yet :S

http://www.sqlservercurry.com/2009/06/simple-family-tree-query-using.html

Also we need to know the depth of the tree (to pass it to the GridControl...) in this example it would be 3 (it takes the max number of parent levels --> -itemA -itemC -itemE)

解决方案

The sample table

create table so1 (Itemid varchar(100), Itemname varchar(100), Itemfatherid varchar(100))
insert so1 select
'itemA','theitemA',null union all select
'itemB','theitemB',null union all select
'itemC','theitemC','itemA' union all select
'itemD','theitemD','itemA' union all select
'itemE','theitemE','itemC' union all select
'itemF','theitemF','itemE' union all select
'itemG','theitemG','itemD'

The query

if OBJECT_ID('tempdb..#tmp') is not null drop table #tmp
;
create table #tmp (
    uniqueid uniqueidentifier not null,
    level int not null,
    itemid varchar(100) null,
    primary key clustered(uniqueid, level)
)
;with cte(level, itemid, parentid, uniqueid) as
(
    select 1, itemid, itemfatherid, NEWID()
    from so1
    where not exists (select * from so1 k where k.itemfatherid=so1.itemid)
    union all
    select cte.level+1, t.itemid, t.itemfatherid, cte.uniqueid
    from cte
    inner join so1 t on t.Itemid = cte.parentid
)
insert #tmp (uniqueid, level, itemid)
select uniqueid, level, itemid
from cte
option (maxrecursion 1000) -- as required
;
;with tmp as (
    select *, newlevel = ROW_NUMBER() over (partition by uniqueid order by level desc)
    from #tmp)
update tmp
set level = newlevel
;
declare @sql nvarchar(max), @columns nvarchar(max)
;
set @sql = CONVERT(nvarchar(max), (
    select number [data()]
    from master..spt_values
    where type='P' and number between 1 and (select MAX(level) from #tmp)
    order by 1
    for xml path('a')))
select @sql = stuff(replace(replace(@sql,'</a><a>','],['),'</a>',']'),1,3,'[')
select @sql = '
select ' + @sql + '
from #tmp
pivot (max(itemid) for level in (' + @sql + ')) v
order by ' + @sql
exec (@sql)

The output

1       2       3       4
itemA   itemC   itemE   itemF
itemA   itemD   itemG   NULL
itemB   NULL    NULL    NULL

这篇关于SQL Server以特定格式显示数据树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！