SQLTeam.com | Weblogs | Forums

Reformatting a tree structure

sql2008

#1

I have the categories of a website structured in my database like this:

HOME|Electronics|Printers
HOME|Electronics|Fax Machines
HOME|Electronics|Scanners
HOME|Outdoor|Tents
HOME|Outdoor|Hiking
HOME|Outdoor|Biking
HOME|Outdoor|Athletic

Now I want to connect it to a software that maps to treeview, the problem is that it expects the table to be structured in this way:

1	HOME
2	Electronics
3	Printers
4	Fax Machines
5	Scanners
6	Outdoor
7	Hiking
8	Biking
9	Athletic

Is it possible to convert from one to the other in SQL? Keeping in mind that main categories (like Electronics) will repeat & I want to make sure that they all get the same index.

I can also do it in another language, but it just seems like it could/should be capable in SQL only.


#2

If you want to keep in the same order you could choose numeric values. Like
2.00 Electronics
2.01 Electronics1

This will maintain in the same sequence of all values