SQL树节点排序 - MS-SQL Server / 应用实例

数据如下
InvCategoryID CategoryName CreateTime ParentID Path ItemType CategoryType ShowIndex 
1 Consumables 2010-04-14 0 1 0 1 2
2 Equipments 2010-04-14 0 2 2 1 1
3 Miscellaneous 2010-04-14 0 3 1 1 4
4 Other 2010-04-14 17:14:28.310 0 4 2 2 3
5 Paper 2010-04-14 18:20:34.607 1 1,5 1 1 0
6 Other 2010-04-14 18:29:53.187 1 1,6 1 1 7
7 Tabulation 2010-04-15 2 2,7 2 1 0
8 Parts 2010-04-20 15:01:05.997 2 2,8 2 1 0
9 Computer 2010-04-20 15:01:24.890 2 2,9 2 1 0
10 Other 2010-04-20 15:02:09.733 2 2,10 2 1 3

要求查询结果按照Path产生树结构，并同时节点按照ShowIndex排序（树结构优先，同级节点按照ShowIndex排序）
例如
CategoryName Path ShowIndex 
Equipments 2 1
Tabulation 2，7 0
Other 2，10 3
Consumables 1 2



附表数据SQL
if exists (select * from sysobjects where id = OBJECT_ID('[EMS_InventoryCategory]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [EMS_InventoryCategory]

CREATE TABLE [EMS_InventoryCategory] (
[InvCategoryID] [int] IDENTITY (1, 1) NOT NULL,
[CategoryName] [varchar] (30) NULL,
[CreateTime] [datetime] NULL DEFAULT (getdate()),
[ParentID] [int] NOT NULL DEFAULT ((0)),
[Path] [varchar] (1000) NULL,
[ItemType] [tinyint] NULL DEFAULT ((0)),
[CategoryType] [tinyint] NULL DEFAULT ((0)),
[ShowIndex] [int] NOT NULL DEFAULT