Im curious why you have underscores in your keys? and what is your desired order? Im assuming your want it orders like it would appear in a directory listing.

Parent 1
Child 1.1
Child 1.2
Child 1.3
Parent 2
Child 2.1
Child 2.2
Child 2.3
Child 2.4

If thats the order your looking for you could do this

  case when parent = "0_" then _key else _parent end + convert(varchar(10),[ItemIndex]) as r1,

I don't think this would work if you went more than 2 levels deep though.

Eric

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