SQL Server 以特定格式显示数据树
我们有一个这样的表格(简化版):
We have a table like this (simplified version):
项目:
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 查询:(更正版本)
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
我们的 ERP 将采用此结果并将其转换为树形控件，如下所示:
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...
CTE 有一些方法，但我们还没有找到解决方案 :S
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
此外，我们需要知道树的深度(将其传递给 GridControl...)，在此示例中它将是 3(它需要最大数量的父级别 --> -itemA -itemC -itemE)
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)
推荐答案
示例表
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'
查询
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)
输出
1 2 3 4
itemA itemC itemE itemF
itemA itemD itemG NULL
itemB NULL NULL NULL
相关文章