Reverse Treeview

Question

Post reply

Reverse Treeview

Alan Stanley

SSChasing Mays

Points: 660
More actions
October 14, 2011 at 12:29 am

#245325

I have a table of results from a CTE that builds treeview data backwards from the branches
I need to somehow consolidate the results back into usable treeview data.
I can achieve the desired result with another CTE but the query takes far too long on 300 records plus.
Surely there must be a simple way to reconstruct this data,having the corrected treelevel seems to be the answer
create table #temp
(
uid int,
cat_parent int,
cat_name varchar(50),
treelevel int
)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(100,0,'Computer',2)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(110,100,'Desktop',1)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(120,110,'ATX',0)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(100,0,'Computer',3)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(110,100,'Desktop',2)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(120,110,'ATX',1)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(130,120,'Green',2)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(100,0,'Computer',2)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(140,100,'Tower',1)
Insert #temp (uid,cat_parent,cat_name,treelevel)
values(150,140,'Blue',2)
--select * from #temp
/* REQUIRED RESULTS
100 0 Computer 0
110 100 Desktop 1
120 110 ATX 2
130 120 Green 3
140 100 Tower 1
150 140 Blue 2
*/

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

bkubicek SSChampion Points: 10948 More actions · Answer 1

You should post your CTE so we can see what you are doing. Based off your post it isn't very clear to me what you want.

tnk SSCrazy Points: 2613 More actions · Answer 2

"Backwards from the branches" isn't descriptive enough for me either. Do you mean a reverse-preorder?

Check out this wikipedia article for some standard nomenclature with regard to tree traversal:

http://en.wikipedia.org/wiki/Tree_traversal

hunt Right there with Babe Points: 780 More actions · Answer 3

Paste your query to understand the requirement

thanks
sarat 🙂
Curious about SQL

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

;WITH tree AS (

SELECT DISTINCT uid, cat_parent, cat_name, node = 0

FROM #temp where cat_parent = 0

UNION ALL

SELECT t.uid, t.cat_parent, t.cat_name, node = l.node+1

FROM tree L

INNER JOIN #temp t ON l.uid = t.cat_parent)

SELECT DISTINCT * FROM tree

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

To add to ChrisM's code, please see the following article on how to display smaller hierarchies likes this...

http://www.sqlservercentral.com/articles/T-SQL/72503/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Jeff Moden (10/14/2011)
To add to ChrisM's code, please see the following article on how to display smaller hierarchies likes this...
http://www.sqlservercentral.com/articles/T-SQL/72503/

Teach a guy to fish - I'd like to add that article to my sig list but it's leaning somewhat already!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Alan Stanley SSChasing Mays Points: 660 More actions · Answer 7

Thanks for all the replies.

The first CTE is being populated bottom up. i.e. we are passing in a list of branch nodes and getting back each node to the root.

However the CTE brings back a repeated list of node data with varying depths that makes it nigh on impossible to calculate the treelevel and get the data into any useable order.

For information the CTE is here

;with cte_get ([uid], [cat_parentId], [cat_name],[cat_meta_title], [treelevel])as

(

select r.[uid], r.cat_parentId, r.cat_name,r.[cat_meta_title], 0

from tbl_product_items p

inner join tbl_product_categories r on p.pr_categoryId = r.[uid]

where p.pr_manufacturerId = 638 and p.pr_status = 1 and r.cat_status = 1

group by r.[uid], r.cat_parentId, r.cat_name

union all

select c.[uid], c.cat_parentId, c.cat_name,c.[cat_meta_title], t.treelevel + 1

from tbl_product_categories c

inner join cte_get t on t.cat_parentId = c.[uid]

)

The results are per the original post

1000Computer2

110100Desktop1

120110ATX0

1000Computer3

110100Desktop2

120110ATX1

130120Green2

1000Computer2

140100Tower1

150140Blue2

But obviously to display this in a treeview I need to deduplicate and recalculate the sortorder, whilst keeping the tree data in order.

REQUIRED RESULTS

100 0 Computer 0

110 100 Desktop 1

120 110 ATX 2

130 120 Green 3

140 100 Tower 1

150 140 Blue 2

We can get the required results using another cte , but there is so much recursion, it takes over 60 seconds with some data.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

Alan Stanley (10/17/2011)
The first CTE is being populated bottom up. i.e. we are passing in a list of branch nodes and getting back each node to the root.

THAT would be a part of the problem. 😉

Now would be a good time to explain a bit more as to why you want to calculate the "upline" for a "list of branch nodes". What is the ultimate goal here?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Stanley SSChasing Mays Points: 660 More actions · Answer 9

Jeff

The tree displays the categories for the result of either a product or manufacturer search for a website.

One solution we came up with was to number each branch from the top down and use that to sort and group, but we were hoping for a more dynamic solution.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

Alan,

This sounds like a mostly static tree. Can you give an estimate on how often the tree may suffer the addition, deletion, or modifcation of at least 1 node or edge (connecting "line")?

Also, would it be fair to say that your "search" may turn up many nodes and that you need the "upline" for all of those nodes sans duplicates?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Stanley SSChasing Mays Points: 660 More actions · Answer 11

Jeff

All your assumptions are correct. We can probably expect one or two changes to the tree per month.

I am currently working on an iterative loop to rebuild the tree data from the repeated results by using a temporary table as adding an order by field would be difficult to maintain. This should work, but if you have a better idea, I would be interested to hear.

Thanks

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

Alan,

I've been asking questions about this because I DO have an idea... 2 actually. I'll try to put it together tonight.

Just to confirm from the forum you've posted in, you're working in SQL Server 2008 for sure? Not a problem if it's 2005 but I do need to know for one of the solutions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Stanley SSChasing Mays Points: 660 More actions · Answer 13

Alan Stanley

SSChasing Mays

Points: 660

October 18, 2011 at 8:43 am

#1396028

Jeff

yes it is 2008

Thanks again

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

I keep looking at this problem and I guess I'm totally confused now. To me anyway, it looks like the code that ChrisM@Work posted several post above this one does the job you want. Have you tried it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)