Recursive CHP on request

Question

I work with hierarchical data and use recursive CTE to list these elements:

Eletronics Televisions Tube LCD Plasma Portable Electronic MP3 Players Flash CD Player Two Way Radios 

My question is:
How to make this list sorted by name and following a hierarchy?

Like this:

 Eletronics Portable Electronic CD Player MP3 Players Flash Two Way Radios Televisions LCD Plasma Tube 

Tks

Answer 1

Here is the correct CTE (asc only)

 ;WITH CTE AS ( SELECT id, id_parent, name_product ,HierarchicalPath = CAST('\'+CAST(name_product AS VARCHAR(MAX)) AS VARCHAR(MAX)) FROM @tab where id_parent is null -- Starts with the first level UNION ALL SELECT t.id, t.id_parent, t.name_product ,HierarchicalPath = CAST(c.HierarchicalPath + '\'+CAST(t.name_product AS VARCHAR(MAX)) AS VARCHAR(MAX)) FROM @tab t INNER JOIN CTE C ON t.id_parent = C.id ) select * from cte order by HierarchicalPath 

Answer 2

Here is the code that I almost did what I want. Now the problem is to sort by desc

 declare @tab table( id int identity(1,1) ,id_parent int ,name_product varchar(100) ) insert into @tab select null, 'Eletronics' union all select 1, 'Televisions' union all select 2, 'Tube' union all select 2, 'LCD' union all select 2, 'Plasma' union all select 1, 'Portable Electronic' union all select 6, 'MP3 Players' union all select 7, 'Flash' union all select 6, 'CD Player' union all select 6, 'Two Way Radios' ;WITH CTE (id,id_parent,name_product,LEVEL,SORTKEY)AS ( SELECT id, id_parent, name_product, 1, CAST(name_product AS VARBINARY(MAX)) FROM @tab where id_parent is null -- Starts with the first level UNION ALL SELECT t.id, t.id_parent, t.name_product, C.LEVEL + 1, CAST(C.SORTKEY + CAST(t.name_product AS VARBINARY(MAX)) AS VARBINARY(MAX)) FROM @tab t INNER JOIN CTE C ON t.id_parent = C.id ) select * from cte order by SORTKEY 

Tks

Answer 3

Option based on @onaiggac (you can use its data :-))

 ;WITH CTE (id, id_parent, name_product, LEVEL, SORTKEY) AS ( SELECT id, id_parent, name_product, 1, CAST(ROW_NUMBER() OVER (ORDER BY name_product) AS VARBINARY(MAX)) FROM @tab where id_parent is null -- Starts with the first level UNION ALL SELECT t.id, t.id_parent, t.name_product, C.LEVEL + 1, C.SORTKEY + CAST(ROW_NUMBER() OVER (ORDER BY t.name_product) AS VARBINARY(MAX)) FROM @tab t INNER JOIN CTE C ON t.id_parent = C.id ) SELECT id, id_parent, REPLICATE(' ', LEVEL - 1) + name_product, LEVEL, SORTKEY FROM CTE ORDER BY SORTKEY 

Trick used here

 ROW_NUMBER() OVER (ORDER BY name_product) 

make an "internal" order. As with @onaiggac, this is then binary compiled in varbinary(max)

 CAST(ROW_NUMBER() OVER (ORDER BY name_product) AS VARBINARY(MAX)) 

which is then recursively supplemented ...

 C.SORTKEY + CAST(ROW_NUMBER() OVER (ORDER BY t.name_product) AS VARBINARY(MAX)) 

Note that ROW_NUMBER() will return bigint ... you can send it to int before directing it to varbinary(max) , e.g.

 CAST(CAST(ROW_NUMBER() OVER (ORDER BY name_product) AS INT) AS VARBINARY(MAX)) 

if you really want ... I don't think it is necessary if your tree is really deep.