une idée sur la command de ces données à plat en dessous de l'ordre?
cette requête simple ne fonctionne pas
select QuestionID , QuestionText , ReferenceID , ParentQuestionID from Question where ReferenceID = 10208 order by ParentQuestionID,QuestionID
données plates
ça devrait être comme ça
Vous pouvez créer la string de la hiérarchie, puis la sortinger. Pour le stockage, vous pouvez utiliser hierarchyid
WITH Question AS ( SELECT * FROM (VALUES (10208, -1), (10209, 10208), (10211, 10208), (10214, 10208), (10212, 10209), (10213, 10209), (10215, 10212) ) Question(QuestionID, ParentQuestionID) ), cte AS ( -- Anchor SELECT *, 0 AS Level, CAST('/' + CAST(QuestionID AS varchar) + '/' AS varchar(100)) AS Hierarchy FROM Question WHERE ParentQuestionID = -1 UNION ALL -- Recursive part SELECT q.*, Level + 1, CAST(Hierarchy + CAST(q.QuestionID AS varchar) + '/' AS varchar(100)) FROM Question q INNER JOIN cte ON q.ParentQuestionID = cte.QuestionID ) SELECT * FROM cte ORDER BY Hierarchy
SQL Fiddle
MODIFIER
Pour order en descendant
WITH Question AS ( SELECT * FROM (VALUES (10208, -1), (10209, 10208), (10211, 10208), (10214, 10208), (10212, 10209), (10213, 10209), (10215, 10212) ) Question(QuestionID, ParentQuestionID) ), cte AS ( -- Anchor SELECT *, 0 AS Level, CAST('/' + CAST(QuestionID AS varchar(max)) + '/' AS varchar(max)) AS Hierarchy FROM Question WHERE ParentQuestionID = -1 UNION ALL -- Recursive part SELECT q.*, Level + 1, CAST(cte.Hierarchy + CAST(q.QuestionID AS varchar(max)) + '/' AS varchar(max)) FROM Question q INNER JOIN cte ON q.ParentQuestionID = cte.QuestionID ), rn AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ParentQuestionID ORDER BY Hierarchy DESC) Seq FROM cte ) SELECT * FROM rn ORDER BY ParentQuestionID, Seq