Printing tree with SQL CTE

Question

Hi

The schema is as follows:

CREATE TABLE [Structure](
    [StructureId] [uniqueidentifier] NOT NULL,
    [SequenceNumber] [int] NOT NULL, -- order for siblings, unique per parent
    [ParentStructureId] [uniqueidentifier] NULL,
 CONSTRAINT [Structure_PK] PRIMARY KEY CLUSTERED 
(
    [StructureId] ASC
)
) ON [PRIMARY]

ALTER TABLE [Structure]  WITH CHECK ADD  CONSTRAINT [Structure_FK1] 
FOREIGN KEY([ParentStructureId])
REFERENCES [Structure] ([StructureId])

Currently, I can get all the logical data out with the follow CTE, but I would like to print it directly in a depth first fashion.

WITH SCTE (StructureId, Level, Seq, ParentId)
AS
(
  SELECT StructureId,  0, SequenceNumber, [ParentStructureId]
    FROM Structure
    WHERE [ParentStructureId] IS NULL 
          AND StructureId = 'F6C5F016-1270-47C1-972F-349C32DFC92A'

  UNION ALL

  SELECT Structure.StructureId, Level + 1, SequenceNumber, ParentStructureId
  FROM Structure
  INNER JOIN SCTE ON SCTE.StructureId = Structure.ParentStructureId
)

SELECT * FROM SCTE
ORDER BY Level, ParentId, Seq

The output is as follows (truncated here):

StructureId                     Level   Seq ParentId
F6C5F016-1270-47C1-972F-349C32DFC92A    0   0   NULL
D2E34429-401A-4A49-9E18-E81CCA0FB417    1   0   F6C5F016-1270-47C1-972F-349C32DFC92A
0CC5E16C-9194-40CA-9F72-1CED2972D7CA    1   1   F6C5F016-1270-47C1-972F-349C32DFC92A
1ECD1D30-EB85-42B0-969F-75794343E3B4    1   2   F6C5F016-1270-47C1-972F-349C32DFC92A
EEC3A981-B790-4600-8CD1-F15972CD9230    2   0   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
4406F639-2F58-4918-A9EF-A4B0F379BEA0    2   1   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
FCAF7870-C606-4AA6-85EE-57B90B1B0CC3    2   2   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
855DF5FB-1593-4E5B-8EF9-3770B45F89D6    2   3   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
3D16DF32-C04F-49B4-B0D9-5BDC9104F810    2   4   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
A1084D00-0198-47D9-87E0-BB8234233F14    2   5   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
CE443C0D-376F-46EC-9914-32C6B7200DB1    2   6   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
0DEA587D-4FCF-414C-AD71-FB00829F8082    2   7   0CC5E16C-9194-40CA-9F72-1CED2972D7CA
CC9FC8D3-254A-486B-8DC4-07E57627476C    2   0   1ECD1D30-EB85-42B0-969F-75794343E3B4
215565CC-501F-4850-B8AE-5466DA5E6854    2   1   1ECD1D30-EB85-42B0-969F-75794343E3B4
D4E6C8E5-5ADD-4AD1-B59B-1A672F66888A    2   2   1ECD1D30-EB85-42B0-969F-75794343E3B4
796C65BF-4714-4DBF-A97A-2150DBE3098C    2   3   1ECD1D30-EB85-42B0-969F-75794343E3B4
B39DEB9C-BE42-43B4-9C38-968399D7D1E2    2   4   1ECD1D30-EB85-42B0-969F-75794343E3B4
6C2F70C6-1DA0-4E1A-BBC1-D7FCAFE6AFEE    2   0   D2E34429-401A-4A49-9E18-E81CCA0FB417
75D7B43B-C971-46B4-BC42-58C3605ADD79    2   1   D2E34429-401A-4A49-9E18-E81CCA0FB417
0B5AAAA0-A69F-431E-86BA-148444D7B1E6    2   2   D2E34429-401A-4A49-9E18-E81CCA0FB417
CB3CF66B-D83A-45E2-953A-6F0CEE094F5B    2   3   D2E34429-401A-4A49-9E18-E81CCA0FB417
1D5F69C3-F036-4667-BD75-A0DC1506DB6D    2   4   D2E34429-401A-4A49-9E18-E81CCA0FB417
71B894F7-B9FC-44DE-AEDB-E6FA026A6082    2   5   D2E34429-401A-4A49-9E18-E81CCA0FB417
F1DFA1E1-013B-449C-9D9D-14C64E75D418    2   6   D2E34429-401A-4A49-9E18-E81CCA0FB417

As you can see, the result is 'breadth first' which makes printing a tree kinda impossible as it is now.

Is there any way (there probably is a trivial way, but my SQL skills are extremely poor) to get the resultant list in 'tree printing friendly' format?

I know I could just dump the results into a program and code the output, but as an exercise I would prefer doing this in SQL itself.

Thanks

Answer 1

Edited after comment. You could add the path to a node, and order on that:

declare @t table (id int, parent int)
insert @t (id, parent) values (1, null), (2,1), (3,2), (4,3), (5,null), (6,5)

; with cte as (
    select  id, parent
    ,       cast(RIGHT(REPLICATE('0',12) + 
                 CONVERT(varchar(12),id),12) as varchar(max)) Path
    from    @t
    where   parent is null
    union all
    select  child.id, child.parent
    ,       parent.Path + RIGHT(REPLICATE('0',12) + 
                                CONVERT(varchar(12),child.id),12) as Path
    from    @t child
    join    cte parent
    on      parent.id = child.parent
)
select  *
from    cte
order by
        Path

This prints the root first, followed by leaves in order. If your id can be larger than 12 digits, increase the number in the char(x) casts.