CREATE TABLE dbo.Test
(
   Id INT NOT NULL PRIMARY KEY CLUSTERED,
   Parent INT,
   name VARCHAR(32),
   code varchar(1024)
);

insert into dbo.Test values (10,null, 'GreatGrandFather',null);
insert into dbo.Test values (2,10, 'GrandFather1',null);
insert into dbo.Test values (1,10, 'GrandFather2',null);
insert into dbo.Test values (3,10, 'GrandMother1',null);
insert into dbo.Test values (4,3, 'Mother1',null);
insert into dbo.Test values (5,3, 'Father1',null);
insert into dbo.Test values (6,5, 'Child1',null);

WITH CTE AS
(
    SELECT
       *,
       CAST(id AS VARBINARY(MAX))  AS OrderingId,
       CAST(Id AS VARCHAR(MAX)) AS AnotherOrderingIdJustForTheFunOfIt
    FROM
       dbo.Test
    WHERE
       Parent IS NULL
    UNION ALL
    SELECT
       t.*,
       c.OrderingId + CAST(t.id AS VARBINARY(MAX)),
       c.AnotherOrderingIdJustForTheFunOfIt + '.' + CAST(t.Id AS VARCHAR(MAX)) 
    FROM
       dbo.Test t
       INNER JOIN CTE c
          ON  c.Id = t.Parent
)
SELECT
   *
FROM
   CTE
ORDER BY
   OrderingId;

Declare @t Table (
	id int,
	parent int,
	Name varchar(10),
	Code varchar(10)
)

Insert Into @t Select 10, null, 'A', null
Insert Into @t Select 3, 10, 'AA', null
Insert Into @t Select 4, 3, 'AAA', null
Insert Into @t Select 6, 10, 'AB', null
Insert Into @t Select 1, null, 'B', null
Insert Into @t Select 8, null, 'C', null
Insert Into @t Select 7, 8, 'CA', null
Insert Into @t Select 5, 8, 'CB', null
Insert Into @t Select 9, 5, 'CBA', null


; with ranked (id, parent, name, code, r)
As (
	Select 
		*,
		r = Row_Number() Over(Partition By parent Order By name)
	From @t
), tiered 
As (
	Select 
		A.*,
		Display = convert(varchar(1000),r),
		Level = 1
	From ranked A
	Where A.parent is null
	
	Union All
	
	Select 
		A.*,
		Display = convert(varchar(1000),replicate('  ',B.Level) + B.Display + '.' + convert(varchar,A.r)),
		Level = B.Level+1
	From ranked A
	Inner Join tiered B
	On B.id = A.parent
	
)

Select * 
From tiered
Order By ltrim(Display)

quote:

---

Originally posted by Seventhnight

Weee...

---

quote:

---

Originally posted by sunitabeck

quote:

---

Originally posted by Seventhnight

Weee...

---

Do you really make those noises and make faces as you are typing the responses?

---