SQL Hierarchical Data

Hi -

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...

Thanks

Select [categoryid], REPLICATE('...', level)  + catname AS catname, [catparentid]
From [dbo].[POM.Categories]
Order By Coalesce([categoryid],[catparentid]), Coalesce([catparentid], 0), [catname]

Select allOpen in new window

sql-table.gif
sql-result.gif