Parent-Child Tree Hierarchical ORDER

I have to following data in SQL Server 2008 R2. SQLFiddle

Schema:

CREATE TABLE [dbo].ICFilters NOT NULL,
[ParentID] [int] NOT NULL DEFAULT 0,
[FilterDesc] varchar NOT NULL,
[Active] [tinyint] NOT NULL DEFAULT 1,
CONSTRAINT [PK_ICFilters] PRIMARY KEY CLUSTERED
( [ICFilterID] ASC ) WITH
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[ICFilters] (ParentID,FilterDesc,Active)
Values
(0,'Product Type',1),
(1,'ProdSubType_1',1),
(1,'ProdSubType_2',1),
(1,'ProdSubType_3',1),
(1,'ProdSubType_4',1),
(2,'PST_1.1',1),
(2,'PST_1.2',1),
(2,'PST_1.3',1),
(2,'PST_1.4',1),
(2,'PST_1.5',1),
(2,'PST_1.6',1),
(2,'PST_1.7',0),
(3,'PST_2.1',1),
(3,'PST_2.2',0),
(3,'PST_2.3',1),
(3,'PST_2.4',1),
(14,'PST_2.2.1',1),
(14,'PST_2.2.2',1),
(14,'PST_2.2.3',1),
(3,'PST_2.8',1)

Table:

| ICFILTERID | PARENTID | FILTERDESC | ACTIVE |
--------------------------------------------------
| 1 | 0 | Product Type | 1 |
| 2 | 1 | ProdSubType_1 | 1 |
| 3 | 1 | ProdSubType_2 | 1 |
| 4 | 1 | ProdSubType_3 | 1 |
| 5 | 1 | ProdSubType_4 | 1 |
| 6 | 2 | PST_1.1 | 1 |
| 7 | 2 | PST_1.2 | 1 |
| 8 | 2 | PST_1.3 | 1 |
| 9 | 2 | PST_1.4 | 1 |
| 10 | 2 | PST_1.5 | 1 |
| 11 | 2 | PST_1.6 | 1 |
| 12 | 2 | PST_1.7 | 0 |
| 13 | 3 | PST_2.1 | 1 |
| 14 | 3 | PST_2.2 | 0 |
| 15 | 3 | PST_2.3 | 1 |
| 16 | 3 | PST_2.4 | 1 |
| 17 | 14 | PST_2.2.1 | 1 |
| 18 | 14 | PST_2.2.2 | 1 |
| 19

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

StackExchange Database Administrators Q#43560, answer score: 26