Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
logpop
Starting Member
16 Posts |
Posted - 2013-05-16 : 09:20:47
|
I have this tree:human..man..womananimal..dog..cat It is saved in db like this:name id parentiddog 1 3 woman 2 4animal 3 -1human 4 -1man 5 4cat 6 3 how do i get this recordset?name orderhuman 1man 2woman 2animal 3dog 4cat 4 basically i need to list rows in order like they would appear in treeview from top to down, so that i can later write these results into a dropdown listbox to show tree structure.i tried using recursive cte but i can only make them work so that they assign nesting level to rows. i used this code:WITH q AS ( SELECT * FROM _ATTmpAdvCats WHERE ParentID = -1 -- this condition defines the ultimate ancestors in your chain, change it as appropriate UNION ALL SELECT m.* FROM _ATTmpAdvCats m JOIN q ON m.parentID = q.AdvCatID )SELECT *FROM q |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-16 : 13:18:56
|
Here is a way to achieve what you want:Run the following query and use sortID and SortNum to group the data in your presentation layer:[CODE]DECLARE @tempa TABLE (name VARCHAR(10), id int, parentid int);INSERT INTO @tempa (name, id, parentid) VALUES ('dog', 1, 3), ('woman', 2, 4),('animal', 3, -1),('human', 4, -1),('man', 5, 4),('cat', 6, 3); SELECT SortNum + parentID as SortID, * from (SELECT (Case when m.parentID = -1 then ID else parentID end) as SortNum , m.* FROM @tempa m) M order by SortNum DESC, parentID, name[/CODE] |
|
|
logpop
Starting Member
16 Posts |
Posted - 2013-05-16 : 17:02:35
|
Thanks man. Your solution works only for one level nested examples but it gave me idea how to solve my problem for unlimited nested trees. Here is my code copied from my project. I use recursive sql and each time add row name to AdvCatOrder, so at the end AdvCatOrder contain full path of treeview items, like this: parent\parent2\child and then i can sort by this.WITH cteBU (ParentId, AdvCatId, AdvCatName, AdvCatOrder) as (-- Anchor member definitionSELECT ANCHOR.ParentId, ANCHOR.AdvCatID, ANCHOR.AdvCatName, --cast (ParentId as nvarCHAR (15)) AS AdvCatOrder, cast (ANCHOR.AdvCatName as nvarchar (99)) as AdvCatOrderFROM dbo._ATTmpAdvCats AS ANCHOR WHERE ANCHOR.ParentId = -1UNION ALL-- Recursive member definitionSELECT RECURSE.ParentId, RECURSE.AdvCatID, RECURSE.AdvCatName,--cast (cast (cteANCHOR.AdvCatOrder as nvarCHAR (15))+'+'+ cast (RECURSE.advcatid as nvarCHAR (15)) as nvarCHAR (15)),cast (cteANCHOR.AdvCatOrder + '\' + RECURSE.AdvCatName as nvarchar (99)) as AdvCatOrderFROM dbo._ATTmpAdvCats AS RECURSEINNER JOIN cteBU AS cteANCHORON RECURSE.ParentId = cteANCHOR.AdvCatId)-- Statement that extracts using the CTESELECT AdvCatId, ParentId, AdvCatName, AdvCatOrderFROM cteBUorder by AdvCatOrderOPTION (Maxrecursion 10000) -- this isn't required; I believe default is 100GO |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-17 : 00:03:37
|
For just ordering you dont need CTE I guessjust below will doSELECT *FROM TableORDER BY COALESCE(NULLIF(parentid,-1),id) DESC, parentid ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-17 : 12:06:37
|
quote: Originally posted by logpop Thanks man. Your solution works only for one level nested examples but it gave me idea how to solve my problem for unlimited nested trees. Here is my code copied from my project. I use recursive sql and each time add row name to AdvCatOrder, so at the end AdvCatOrder contain full path of treeview items, like this: parent\parent2\child and then i can sort by this.WITH cteBU (ParentId, AdvCatId, AdvCatName, AdvCatOrder) as (-- Anchor member definitionSELECT ANCHOR.ParentId, ANCHOR.AdvCatID, ANCHOR.AdvCatName, --cast (ParentId as nvarCHAR (15)) AS AdvCatOrder, cast (ANCHOR.AdvCatName as nvarchar (99)) as AdvCatOrderFROM dbo._ATTmpAdvCats AS ANCHOR WHERE ANCHOR.ParentId = -1UNION ALL-- Recursive member definitionSELECT RECURSE.ParentId, RECURSE.AdvCatID, RECURSE.AdvCatName,--cast (cast (cteANCHOR.AdvCatOrder as nvarCHAR (15))+'+'+ cast (RECURSE.advcatid as nvarCHAR (15)) as nvarCHAR (15)),cast (cteANCHOR.AdvCatOrder + '\' + RECURSE.AdvCatName as nvarchar (99)) as AdvCatOrderFROM dbo._ATTmpAdvCats AS RECURSEINNER JOIN cteBU AS cteANCHORON RECURSE.ParentId = cteANCHOR.AdvCatId)-- Statement that extracts using the CTESELECT AdvCatId, ParentId, AdvCatName, AdvCatOrderFROM cteBUorder by AdvCatOrderOPTION (Maxrecursion 10000) -- this isn't required; I believe default is 100GO
Brilliant! |
|
|
|
|
|
|
|