More advanced logic for ordering results of a query in T-SQL?

Question

I am currently writing a SQL query that should display a tree-view of areas inside a building with areas, sub-areas, etc. Unfortunately I have been unable to mimic the ordering used by some of our software tools. I'm restricted to MS SQL 2000 so the question of order becomes much more complicated and I'm just over my head at this point.

The ordering logic is that the Child column and Parent column are related. If the value of the 1st row's Child column matches the 2nd row's Parent column, then the 2nd row goes after the first.

--How it currently returns data
Child Level      Parent
562   Campus      0
86  Area        1
87  Area        1
88  Area        1
90  Sub-Area    86
91  Sub-Area    86
92  Sub-Area    87
93  Sub-Area    87
94  Sub-Area    88
95  Sub-Area    88
3    Unit        90
16    Unit      90
4    Unit        91
6    Unit        91
etc, so on and therefore

--How I want it to return the data
Child Level         Parent
562 Campus          0
1   Building        562
86  Area            1
90  Sub-Area        86
91  Sub-Area        86
87  Area            1
95  Sub-Area        87   
95  Sub-Area        87 

For this logic to work correctly it would need to do something like

1. Return the building rows with their Parent and Child codes
2. Match Area Parent codes to the building Child codes, then insert Area rows under the appropriate Building row.
3. Match Sub-Area Parent codes to the Area Child codes, then insert Sub-Area rows under the appropriate Area.
4. Match Unit Parent codes to the Sub-Area Child codes, then insert the Unit rows under the appropriate Sub-Area

If this actually possible with SQL?

I would love to know if it is as I'm hesitant to invest any more time into this unless I know it's actually a possibility. I realize that I could write a CASE statement with a custom mapping for an ORDER BY statement, but that won't work for any other campus (parent/child codes are different) and I would love to be able to re-use this code in the future with minimal customization.

Thanks!

EDIT: Adding Query as requested

DECLARE
@BuildingType   int,
@CampusType int

SET @BuildingType= 4
SET @CampusType= 1

select 

b.fkabc_building_child,
(select isnull(c.collectionname, 'none') 
from abc_collections c
where c.pkabc_collections = b.fkabc_building_child) as 'Child Collection', 
l.floorname,
isnull(b.fkabc_collections_parent,0) as fkabc_collections_parent,
b.fkabc_floorbreakdowns

from abc_breakdowns r
left join abc_floorbreakdowns fr 
on fr.pkabc_floorbreakdowns = b.fkabc_floorbreakdowns
inner join abc_buildingtypescampustypes btct
on btct.pkabc_buildingtypescampustypes = fr.fkabc_buildingtypescampustypes
inner join abc_buildingtypes bt
on btct.fkabc_buildingtypes = bt.pkabc_buildingtypes
inner join abc_collectiontypes ct
on btct.fkabc_collectiontypes = ct.pkabc_collectiontypes
inner join abc_collections c
on b.fkabc_building_child = c.pkabc_collections
inner join abc_floors l
on l.pkabc_floors = c.fkabc_floors

where bt.pkabc_buildingtypes = @BuildingType
and ct.pkabc_collectiontypes = @CampusType

Answer 1

Here's one approach; very procedural. Unfortunately on SQL Server 2000 I don't think you'll be able to get away from cursors unless you use a solution like Peter's which is limited to 5 levels and hard-codes the types of levels into the query itself (mixing data and metadata). You'll have to weigh these limitations with any observable performance difference.

Note that I didn't add any handling for circular references, so hopefully you are preventing that from happening in other ways.

SET NOCOUNT ON;
GO

DECLARE @foo TABLE
(
 AreaID INT PRIMARY KEY,
 [Level] SYSNAME, 
 ParentAreaID INT
);

INSERT @foo 
SELECT           562, 'Campus',   0
UNION ALL SELECT 86,  'Area',     1
UNION ALL SELECT 87,  'Area',     1
UNION ALL SELECT 88,  'Area',     1
UNION ALL SELECT 90,  'Sub-Area', 86
UNION ALL SELECT 91,  'Sub-Area', 86
UNION ALL SELECT 92,  'Sub-Area', 87
UNION ALL SELECT 93,  'Sub-Area', 87
UNION ALL SELECT 94,  'Sub-Area', 88
UNION ALL SELECT 95,  'Sub-Area', 88
UNION ALL SELECT 3,   'Unit',     90
UNION ALL SELECT 16,  'Unit',     90
UNION ALL SELECT 4,   'Unit',     91
UNION ALL SELECT 6,   'Unit',     91
UNION ALL SELECT 1,   'Building', 562;

DECLARE @nest TABLE
(
 NestID INT IDENTITY(1,1) PRIMARY KEY,
 AreaID INT,
 [Level] INT,
 ParentNestID INT,
 AreaIDPath VARCHAR(4000)
);

DECLARE @rc INT, @l INT;

SET @l = 0;

INSERT @nest(AreaID, [Level], AreaIDPath) 
 SELECT AreaID, 0, CONVERT(VARCHAR(12), AreaID)
 FROM @foo
 WHERE ParentAreaID = 0;

SELECT @rc = @@ROWCOUNT;

WHILE @rc >= 1
BEGIN
 SELECT @l = @l + 1;

 INSERT @nest(AreaID, [Level], ParentNestID)
  SELECT f.AreaID, @l, n.NestID
   FROM @foo AS f
   INNER JOIN @nest AS n
   ON f.ParentAreaID = n.AreaID
   AND n.[Level] = @l - 1;

 SET @rc = @@ROWCOUNT;

 UPDATE n
  SET n.AreaIDPath = COALESCE(n2.AreaIDPath, '') 
   + '\' + CONVERT(VARCHAR(12), n.AreaID) + '\'
     FROM @nest AS n
     INNER JOIN @nest AS n2
     ON n.ParentNestID = n2.NestID
     WHERE n.[Level] = @l
     AND n2.AreaIDPath NOT LIKE '%\' + CONVERT(VARCHAR(12), n.AreaID) + '\%';
END

SELECT
 structure = REPLICATE(' - ', n.[Level]) + RTRIM(f.AreaID), 
 f.AreaID, f.[Level], f.ParentAreaID 
FROM @nest AS n
INNER JOIN @foo AS f
ON n.AreaID = f.AreaID
ORDER BY n.AreaIDPath;

This really is what recursive CTEs in SQL Server 2005 were designed for. (This is still essentially a cursor, but the syntax is much cleaner than the above mess.) Until you can upgrade to SQL Server 2005, you might have better luck simply using the presentation tier to loop over the resultset and order things appropriately, if this is too complex to introduce to your query operations.

Answer 2

Something like this:

-- prepare some test data
declare @table table (Child int, [Level] varchar(30), Parent int)

insert @table values (562 , 'Campus  ',  0  )
insert @table values (1   , 'Building',  562)
insert @table values (86  , 'Area    ',  1  )
insert @table values (87  , 'Area    ',  1  )
insert @table values (88  , 'Area    ',  1  )
insert @table values (90  , 'Sub-Area',  86 )
insert @table values (91  , 'Sub-Area',  86 )
insert @table values (92  , 'Sub-Area',  87 )
insert @table values (93  , 'Sub-Area',  87 )
insert @table values (94  , 'Sub-Area',  88 )
insert @table values (95  , 'Sub-Area',  88 )
insert @table values (3   , 'Unit    ',  90 )
insert @table values (16  , 'Unit    ',  90 )
insert @table values (4   , 'Unit    ',  91 )
insert @table values (6   , 'Unit    ',  91 )

select
  a.Child, a.[Level], a.Parent
, Campus = 
    case a.[Level]
      when 'Unit'     then e.Child
      when 'Sub-Area' then d.Child
      when 'Area'     then c.Child
      when 'Building' then b.Child
      when 'Campus'   then a.Child
    end
, Building = 
    case a.[Level]
      when 'Unit'     then d.Child
      when 'Sub-Area' then c.Child
      when 'Area'     then b.Child
      when 'Building' then a.Child
    end
, Area = 
    case a.[Level]
      when 'Unit'     then c.Child
      when 'Sub-Area' then b.Child
      when 'Area'     then a.Child
    end
, Sub_Area = 
    case a.[Level]
      when 'Unit'     then b.Child
      when 'Sub-Area' then a.Child
    end
, Unit = 
    case a.[Level]
      when 'Unit'     then a.Child
    end

from @table a

left join @table b on a.Parent = b.Child 
  and ((a.[Level] = 'Unit'     and b.[Level] = 'Sub-Area')
    or (a.[Level] = 'Sub-Area' and b.[Level] = 'Area'    )
    or (a.[Level] = 'Area'     and b.[Level] = 'Building')
    or (a.[Level] = 'Building' and b.[Level] = 'Campus'  ))

left join @table c on b.Parent = c.Child 
  and ((b.[Level] = 'Sub-Area' and c.[Level] = 'Area'    )
    or (b.[Level] = 'Area'     and c.[Level] = 'Building')
    or (b.[Level] = 'Building' and c.[Level] = 'Campus'  ))

left join @table d on c.Parent = d.Child 
  and ((c.[Level] = 'Area'     and d.[Level] = 'Building')
    or (c.[Level] = 'Building' and d.[Level] = 'Campus'  ))

left join @table e on d.Parent = e.Child 
  and ((d.[Level] = 'Building' and e.[Level] = 'Campus'  ))

order by 
  4, 5, 6, 7, 8

There's probably a cleverer way to do it w/less repetition, but it alludes me for now.

Now, this code is just for demonstration, to illustrate how the query works. You don't need to have 5 sort fields in the SELECT, you can move them to the ORDER BY. And you shouldn't use ordinal positions in the ORDER BY.

But you do need the 4 joins, and the conditional join logic, to pull out the parent levels for each child. And you do need the CASE statements, to pull out the sort key for each level.

Perhaps you could wrap the SELECT statement in a derived table, and move the ORDER BY to the outer query. eg:

SELECT Child, [Level], Parent
FROM (
  SELECT ....
  ) a
ORDER BY Campus, Building, Area, Sub_Area, Unit

Answer 3

I would have to spend more time looking into it to figure out the details... but, if you are using SQL Server 2005 (or 2008), I would suggest looking into using a Common Table Expression (CTE). This lets you build the query recursively; so you can fetch a building, then fetch all of its children to add in to the list. You may be able to come up with a numbering scheme or the like to get the entries in the proper order using a CTE.