SQL Server to show a data tree in a specific format

Question

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)

You would need dynamic SQL for dynamic columns. Not particularly straightforward to do in TSQL. Can't your code deal with it in row format? — Martin Smith, Mar 15 '11 at 16:16
Well its a sql query as string in c# that is passed in the control call like Grid.Render("sql"); so some logic could be implemented in c# but we prefer to handle it in the query itself... — VSP, Mar 15 '11 at 16:36

RichardTheKiwi · Accepted Answer · 2011-03-16T08:52:39.033

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

Pretty impresive logic...unfortunatelly the format is not like the example we provided (sorry we discovered today that we had written it wrong) if you dont mind to take the time to check it again and change the awnser acordingly...if not no problem it was useful enough... :) — VSP, Mar 16 '11 at 08:47
@ase69s - updated with a single change, WHERE clause added to start only from leaf nodes — RichardTheKiwi, Mar 16 '11 at 08:53
Works like a charm, only one more thing, how would be the query to get the maximum number of parents (was added at the end of the question previously) — VSP, Mar 17 '11 at 11:19
@ase69s That is trivially checked from .Net against the Dataset field count.. I can't see any brilliant way for indicating that using any row or column of the data — RichardTheKiwi, Mar 17 '11 at 11:24

SQL Server to show a data tree in a specific format

1 Answers1

The sample table

The query

The output

Linked