SQL Query listing the Space Hierarchy in order, with the names indented

Version 1

    THe following is using SQL*Server syntax.  Thought I'd share it.

     

    /* This query will list the Jive Space Hierarchy in order, with the names indented
    based upon the level each space is at.
    The Spaces (table JiveCommunity) use a TREE structure based upon lft and rgt values in each row. 
    Each row does not have a 'parent Space' column, only the lft and rgt values to organize the hierarchy.
    Thanks for tip at
      http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f686ccb9-e936-4cfd-af6a-a52a0ed56b26/does-a-nested-set-model-defy-normalization?forum=databasedesign
    */


    use core
    GO
          
    IF OBJECT_ID('tempdb..#SPACE_INDENTION') IS NOT NULL DROP TABLE #SPACE_INDENTION             
    GO 

     

    -- 1st, calculate amount of indententations needed to put at beginning of Space_Names
    --   so they appear at correct levels; set them aside. See #4 in above linked article
    SELECT S1.lft, COUNT(S2.communityID) AS indentation, S1.communityID
       into #SPACE_INDENTION 
    FROM jiveCommunity AS S1, jiveCommunity AS S2
    WHERE S1.lft BETWEEN S2.lft AND S2.rgt
    GROUP BY S1.communityID, S1.lft
    GO

     

    -- Summary Report at top
    SELECT count(communityID) as Number_of_Spaces,
    , convert(varchar,GETDATE(),120) as when__query_run
      FROM jiveCommunity
       GO

     

    --Details
    select space(indent.indentation * 3) + name as Space_Name, sp.description, SP.communityID
    , convert(varchar,(dateadd(s,([creationDate]/1000)-14400,'1970-01-01')),120) as creationDate_
    , convert(varchar,(dateadd(s,([modificationDate]/1000)-14400,'1970-01-01')),120) as modificationDate_
    from  #SPACE_INDENTION Indent, jiveCommunity SP
    where Indent.communityID = SP.communityID
    order by Indent.lft