Below is my sql 2008 query to attempt to output hierarchical data. My table is in the attached image "sql-table" and the result set is attached as "sql-result"
The REPLICATE works find but the order of the categories is not
For example the categoryid:133 has a catparentid of 131 so should fall directly under the 131 row. Also if I add a "child" to a "child"... two levels; the order get even worse.
I am not sure if my query is the best way to get hierarchical data (treeview type data)
Open to suggestions...
Select [categoryid], REPLICATE('...', level) + catname AS catname, [catparentid]
Order By Coalesce([categoryid],[catparentid]), Coalesce([catparentid], 0), [catname]