I'm trying to get a listing of reports from a table called listbaritems. The table has parent, _key and itemindex. So for example, there may be a header (parent record) called "Case Reports" and it's parent value is always 0_ (varchar) and it's _key may be 27_ (varchar). The itemindex on all parents are 0 (int). A child record of this parent, will have a parent value of 27_, _Key=28, ItemIndex=1 and another 1 value of 27_, _Key=29, ItemIndex=2 and a third value of 27_, _Key=40, ItemIndex=3.

The problem I'm having is that the _Key value is a unique incrementing field. So the records can't be ordered by _Key because they won't stay with the parent, they can't be ordered by Parent, since all headers have 0 they always end up on top.

Below is a view of the query and data I have been trying. Any help greatly appreciated:

SELECT TOP 100 PERCENT 
	(rtrim([Parent]) + rtrim([_Key])) + convert(varchar(10),[ItemIndex]) as r1,
	parent,
	_key, 
	itemindex, 
	itemtext, 
	itemobject
FROM    dbo.listbaritems
WHERE     (itemform = 'ctreports')
ORDER BY 1

Text of Current Output

r1         parent    _key  itemindex   itemtext 
------------------------------------------------- 
0_21_0     0_        21_    0           Schedule Reports
0_27_0     0_        27_    0           Case Reports
0_31_0     0_        31_    0           Procedure Reports
0_34_0     0_        34_    0           Surgeon Activity
0_41_0     0_        41_    0           Anesthetist Reports
0_43_0     0_        43_    0           Department Logs
0_47_0     0_        47_    0           Resource/Inventory 
21_22_1    21_       22_    1           Scheduled Case Listing
21_23_2    21_       23_    2           Scheduler Activity Report
21_24_3    21_       24_    3           Block Utilization
21_26_4    21_       26_    4           Cancelled Case Listing
21_56_5    21_       56_    5           Staff Case Activity
21_57_6    21_       57_    6           Start Versus Actual
27_28_1    27_       28_    1           Pending Cases
27_29_2    27_       29_    2           Completed Cases
27_62_3    27_       62_    3           Case Activity Log
27_63_4    27_       63_    4           Room Utilization
27_65_5    27_       65_    5           Room Util. 1st Shift
30_32_1    30_       32_    1           Cost Report
30_32_2    30_       32_    2           Cost By Patient
30_33_3    30_       33_    3           Cost By Surgeon
30_54_4    30_       54_    4           Top Procedure Report
30_71_5    30_       71_    5           Cost By Payor