Obtenir une structure hiérarchique à l'aide de SQL Server

J'ai une table auto-référencée avec une key primaire, id et une key étrangère parent_id .

 +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PK | NULL | IDENTITY | | parent_id | int(11) | YES | | NULL | | | name | varchar(255) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+

J'ai une table comme suit (réduire datatables pour plus clair)

Tableau MySiteMap

  Id Name parent_id 1 A NULL 2 B 1 3 C 1 4 D 1 20 B1 2 21 B2 2 30 C1 3 31 C2 3 40 D1 4 41 D2 4

Je voudrais get la structure hiérarchique en utilisant la requête SQL Server:

  A | B | | B1 | B2 C | | C1 | C2 D | | D1 | D2

Aucune suggestion?

Vous pouvez utiliser des expressions de table communes .

 WITH LeveledSiteMap(Id, Name, Level) AS ( SELECT Id, Name, 1 AS Level FROM MySiteMap WHERE Parent_Id IS NULL UNION ALL SELECT m.Id, m.Name, l.Level + 1 FROM MySiteMap AS m INNER JOIN LeveledSiteMap AS l ON m.Parent_Id = l.Id ) SELECT * FROM LeveledSiteMap

Utilisez ceci:

 ;WITH CTE(Id, Name, parent_id, [Level], ord) AS ( SELECT MySiteMap.Id, CONVERT(nvarchar(255), MySiteMap.Name) AS Name, MySiteMap.parent_id, 1, CONVERT(nvarchar(255), MySiteMap.Id) AS ord FROM MySiteMap WHERE MySiteMap.parent_id IS NULL UNION ALL SELECT MySiteMap.Id, CONVERT(nvarchar(255), REPLICATE(' ', [Level]) + '|' + REPLICATE(' ', [Level]) + MySiteMap.Name) AS Name, MySiteMap.parent_id, CTE.[Level] + 1, CONVERT(nvarchar(255),CTE.ord + CONVERT(nvarchar(255), MySiteMap.Id)) AS ord FROM MySiteMap JOIN CTE ON MySiteMap.parent_id =CTE.Id WHERE MySiteMap.parent_id IS NOT NULL ) SELECT Name FROM CTE ORDER BY ord

Pour ça:

 A | B | B1 | B2 | C | C1 | C2 | D | D1 | D2

Je sais que changer la structure d'une table est toujours une opération critique mais depuis le server sql 2008 a introduit le type de données HierarchyId j'aime vraiment travailler avec elle. Peut-être jeter un oeil à:

http://www.codeproject.com/Articles/37171/HierarchyID-Data-Type-in-SQL-Server http://www.codeproject.com/Tips/740553/Hierarchy-ID-in-SQL-Server

Je suis sûr que vous comprendrez rapidement comment utiliser ce type de données et ses fonctions. Le code SQL utilisant ce type de données est plus structuré et offre de meilleures performances que le CTE.

J'ai commencé avec une requête, (mais quand je la vérifie maintenant, elle est similaire à Mark.)

Je vais l'append quand même , alors que j'ai créé aussi un sqlfiddle avec le mien et la requête Mark.

 WITH tList (id,name,parent_id,nameLevel) AS ( SELECT t.id, t.name, t.parent_id, 1 AS nameLevel FROM t as t WHERE t.parent_id IS NULL UNION ALL SELECT tnext.id, tnext.name, tnext.parent_id, tList.nameLevel + 1 FROM t AS tnext INNER JOIN tList AS tlist ON tnext.parent_id = tlist.id ) SELECT id,name,isnull(parent_id,0) 'parent_id',nameLevel FROM tList order by nameLevel;

Un bon blog: SQL Query – Comment get des données dans la structure hiérarchique?