Parent-Child Tabellen für Reports sortieren
Wenn man eine Parent-Child-Tabelle für einen Report vorbereiten soll, so ist das nicht immer ganz einfach. Die Parent-Child-Hierarchy kann man zwar noch mit einer rekursiven CTE (common table expression) abfragen, jedoch ist die anschließende Sortierung des Resultsets ein nicht zu unterschätzendes Hindernis.
Die CTE-Abfrage liefert zwar die richtigen Datensätze, gibt sie aber nicht in einer, für den Report geeigneten Weise, zurück.
Beispiel eines Ergebnis-Sets für eine Abfrage der Personal-Hierarchy aus der AdventureWorks:
Title EmployeeID ManagerID Level ------------------------------- ----------- ----------- ------ Chief Executive Officer 109 NULL 0 Marketing Manager 6 109 1 Vice President of Engineering 12 109 1 Information Services Manager 42 109 1 Chief Financial Officer 140 109 1 Vice President of Production 148 109 1 Vice President of Sales 273 109 1 North American Sales Manager 268 273 2 European Sales Manager 284 273 2 Pacific Sales Manager 288 273 2 Sales Representative 290 288 3 Sales Representative 285 284 3 Sales Representative 286 284 3 Sales Representative 289 284 3 Sales Representative 275 268 3
Wie man hier sehen kann, werden die unterschiedlichen Hierarchy-Level sortiert dargestellt.
Für einen Bericht müsste man dieses Ergebnis umsortieren bzw. in der folgenden Reihenfolge zurückgeliefert bekommen:
Title EmployeeID ManagerID LoginID Level ---------------------------------- ----------- ----------- ---------------------------- ------ Chief Executive Officer 109 NULL adventure-works\ken0 0 Vice President of Engineering 12 109 adventure-works\terri0 1 Engineering Manager 3 12 adventure-works\roberto0 2 Design Engineer 11 3 adventure-works\jossef0 3 Research and Development Manager 158 3 adventure-works\dylan0 3 Research and Development Engineer 114 158 adventure-works\gigi0 4 Research and Development Manager 217 158 adventure-works\michael6 4 Research and Development Engineer 79 158 adventure-works\diane1 4 Senior Tool Designer 263 3 adventure-works\ovidiu0 3 Tool Designer 265 263 adventure-works\janice0 4 Tool Designer 5 263 adventure-works\thierry0 4 Senior Design Engineer 267 3 adventure-works\michael8 3
Hier ist die Reihenfolge, beginnend mit einem Manager ab Level 1 für diesen einen Manager bis zum Boden definiert. Danach wird zur nächst höheren Ebene zurückverwiesen die wiederum weitere Untergebene hat. Das Ergebnis wird also so angezeigt, wie dies in einem Bericht normalerweise stattfinden sollte.
Diese Sortierung kann man bereits bei der Generierung der Hierarchy als extra Feld mit in die rekursive Abfrage einbauen. Man benötigt hierfür ein Feld dass sortierbar ist und für den individuellen Zweck auch geeignet scheint.
Ich hab für das Beispiel einfach die EmployeeID verwendet. Wenn man diese ID je Ebene jeweils mit den IDs der Vorgesetzten versieht dann erhält man in etwa die folgende Spalte:
Hierarchy -------------- 109 1096 10912 10942 109140 109148 109273 109273268 109273284 109273288 109273288290 109273284285 109273284286 109273284289 109273268275
Sortiert man das Ergebnis nach dieser Hierarchy-Spalte und rückt das Resultset je nach Level noch ein wird die Hierarchy schnell sichtbar.
| Chief Executive Officer (109) ————— Vice President of Engineering (12) ————————— Engineering Manager (3) ————————————— Design Engineer (11) ————————————— Research and Development Manager (158) ————————————————— Research and Development Engineer (114) ————————————————— Research and Development Manager (217) ————————————————— Research and Development Engineer (79) ————————————— Senior Tool Designer (263) ————————————————— Tool Designer (265) ————————————————— Tool Designer (5) ————————————— Senior Design Engineer (267) ————————————— Design Engineer (270) ————————————— Senior Tool Designer (4) ————————————— Design Engineer (9) ————— Chief Financial Officer (140) ————————— Assistant to the Chief Financial Officer (103) ————————— Accounts Manager (139) ————————————— Accounts Receivable Specialist (130) ————————————— Accounts Payable Specialist (166)
Das Ergebnis enthält ja die EmployeeID und kann somit mit weiteren Tabellen verjoint werden und für einen Bericht sozusagen Mundgerecht aufbereitet werden. Die Sortierung innerhalb der Ebenen erfolgt im Moment noch über die EmployeeID, könne jedoch ggf. noch durch die JobBezeichung ergänzt werden. Dies würde die einzelnen Tätigkeiten auf den inneren Ebenen noch ein wenig gruppieren.
Wie immer hier das komplette Listing:
| T-SQL | | copy code | | ? |
| 01 | USE AdventureWorks |
| 02 | |
| 03 | SET NOCOUNT ON |
| 04 | |
| 05 | DECLARE @EmployeeID INT |
| 06 | |
| 07 | -- ------------------------------------------------------------------ |
| 08 | -- CEO bestimmen (Start der Hierarchy) |
| 09 | -- ------------------------------------------------------------------ |
| 10 | |
| 11 | SELECT @EmployeeID = EmployeeID |
| 12 | FROM HumanResources.Employee |
| 13 | WHERE ManagerID IS NULL |
| 14 | |
| 15 | -- ------------------------------------------------------------------ |
| 16 | -- Parent-Child Struktur parsen und einen sortierbaren Schlüssel |
| 17 | -- aufbauen. |
| 18 | -- In diesem Beispiel ist dies die EmployeeID, könnte auch jedes |
| 19 | -- andere Feld sein das eine Sortierung möglich machen könnte. |
| 20 | -- ------------------------------------------------------------------ |
| 21 | |
| 22 | ;WITH ParentChildHierachy AS |
| 23 | ( |
| 24 | SELECT emp.Title |
| 25 | ,emp.EmployeeID |
| 26 | ,emp.ManagerID |
| 27 | ,emp.LoginID |
| 28 | ,0 AS LEVEL |
| 29 | ,CONVERT(VARCHAR(MAX), |
| 30 | CONVERT(VARCHAR(10),emp.EmployeeID)) AS Hierarchy |
| 31 | FROM HumanResources.Employee emp |
| 32 | WHERE emp.EmployeeID = @EmployeeID |
| 33 | |
| 34 | UNION ALL |
| 35 | |
| 36 | SELECT emp.Title |
| 37 | ,emp.EmployeeID |
| 38 | ,emp.ManagerID |
| 39 | ,emp.LoginID |
| 40 | ,pch.LEVEL+1 |
| 41 | ,CONVERT(VARCHAR(MAX),pch.Hierarchy |
| 42 | +CONVERT(VARCHAR(10),emp.EmployeeID)) AS Hierarchy |
| 43 | FROM HumanResources.Employee emp |
| 44 | join ParentChildHierachy pch ON pch.EmployeeID = emp.ManagerID |
| 45 | ) |
| 46 | SELECT Title |
| 47 | ,EmployeeID |
| 48 | ,ManagerID |
| 49 | ,LoginID |
| 50 | ,LEVEL |
| 51 | ,Hierarchy |
| 52 | INTO #TempTable |
| 53 | FROM ParentChildHierachy |
| 54 | ORDER BY Hierarchy |
| 55 | |
| 56 | -- ------------------------------------------------------------------ |
| 57 | -- Ausgabe der Hierarchie in Druckbarer Form |
| 58 | -- ------------------------------------------------------------------ |
| 59 | |
| 60 | |
| 61 | DECLARE @Title VARCHAR(1000) |
| 62 | DECLARE @LEVEL INT |
| 63 | |
| 64 | DECLARE c1 CURSOR FOR |
| 65 | SELECT LEVEL,Title,EmployeeID FROM #TempTable |
| 66 | |
| 67 | OPEN c1 |
| 68 | |
| 69 | FETCH NEXT FROM c1 INTO @LEVEL, @Title, @EmployeeID |
| 70 | |
| 71 | WHILE @@FETCH_STATUS = 0 |
| 72 | BEGIN |
| 73 | PRINT CASE @LEVEL WHEN 0 THEN '|' ELSE CHAR(151) END |
| 74 | + REPLICATE(CHAR(151),@LEVEL*4) |
| 75 | + ' ' |
| 76 | + @Title |
| 77 | + ' (' + CONVERT(VARCHAR(10),@EmployeeID) + ')' |
| 78 | |
| 79 | FETCH NEXT FROM c1 INTO @LEVEL, @Title, @EmployeeID |
| 80 | END |
| 81 | |
| 82 | CLOSE c1 |
| 83 | DEALLOCATE c1 |
| 84 | |
| 85 | DROP TABLE #TempTable |
| 86 |
have fun
Facebook
XING
META-SQL