SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 show tree in order
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

logpop
Starting Member

16 Posts

Posted - 05/16/2013 :  09:20:47  Show Profile  Reply with Quote
I have this tree:

human
..man
..woman
animal
..dog
..cat


It is saved in db like this:

name  id parentid
dog    1  3       
woman  2  4
animal 3  -1
human  4  -1
man    5  4
cat    6  3


how do i get this recordset?

name   order
human  1
man    2
woman  2
animal 3
dog    4
cat    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

547 Posts

Posted - 05/16/2013 :  13:18:56  Show Profile  Reply with Quote
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:

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


Go to Top of Page

logpop
Starting Member

16 Posts

Posted - 05/16/2013 :  17:02:35  Show Profile  Reply with Quote
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 definition
SELECT ANCHOR.ParentId, ANCHOR.AdvCatID, ANCHOR.AdvCatName,
	--cast (ParentId as nvarCHAR (15)) AS AdvCatOrder,
	cast (ANCHOR.AdvCatName as nvarchar (99)) as AdvCatOrder
FROM dbo._ATTmpAdvCats AS ANCHOR WHERE ANCHOR.ParentId = -1
UNION ALL
-- Recursive member definition
SELECT 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 AdvCatOrder
FROM dbo._ATTmpAdvCats AS RECURSE
INNER JOIN cteBU AS cteANCHOR
ON RECURSE.ParentId = cteANCHOR.AdvCatId)
-- Statement that extracts using the CTE
SELECT AdvCatId, ParentId, AdvCatName, AdvCatOrder
FROM cteBU
order by AdvCatOrder
OPTION (Maxrecursion 10000) -- this isn't required; I believe default is 100
GO
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/17/2013 :  00:03:37  Show Profile  Reply with Quote
For just ordering you dont need CTE I guess
just below will do

SELECT *
FROM Table
ORDER BY COALESCE(NULLIF(parentid,-1),id) DESC, parentid


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/17/2013 :  12:06:37  Show Profile  Reply with Quote
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 definition
SELECT ANCHOR.ParentId, ANCHOR.AdvCatID, ANCHOR.AdvCatName,
	--cast (ParentId as nvarCHAR (15)) AS AdvCatOrder,
	cast (ANCHOR.AdvCatName as nvarchar (99)) as AdvCatOrder
FROM dbo._ATTmpAdvCats AS ANCHOR WHERE ANCHOR.ParentId = -1
UNION ALL
-- Recursive member definition
SELECT 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 AdvCatOrder
FROM dbo._ATTmpAdvCats AS RECURSE
INNER JOIN cteBU AS cteANCHOR
ON RECURSE.ParentId = cteANCHOR.AdvCatId)
-- Statement that extracts using the CTE
SELECT AdvCatId, ParentId, AdvCatName, AdvCatOrder
FROM cteBU
order by AdvCatOrder
OPTION (Maxrecursion 10000) -- this isn't required; I believe default is 100
GO




Brilliant!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000