Hierarchy - How to generate sequence numbers for hierarchical data in sql server

De openkb
Aller à : Navigation, rechercher

Sommaire

Questions

I have create a function in sql to get a serial number in hierarchy.I have table called Goals.the structure of table is below

 GoalId      ParentId   Goalstatement
----------   ----------  ----------
1               0          abc
2               0          def
3               1          acc
4               2          efc
5               3          dec
6               0          efc
7               3          jhg

I want to write a function to get the result as

Serial no       GoalId           ParentId        GoalStatement
----------       ----------       ----------     --------------------
1                1                 0              
2                2                 0
3                6                 0
1.1              3                 1
1.1.1            5                 3
1.1.2            7                 3
2.1              4                 2

----------

I have tried with common table expression

WITH Hierarchy(GoalID, ParentId, Parents)
AS
(
    SELECT GoalID,  GoalParentID, CAST(   AS VARCHAR(MAX))
        FROM Goals AS FirtGeneration
        WHERE GoalParentID =0    
    UNION ALL
    SELECT NextGeneration.GoalID, NextGeneration.GoalParentID, 
    CAST(CASE WHEN Parent.Parents =   
        THEN(CAST(NextGeneration.GoalParentID AS VARCHAR(MAX)))
        ELSE(Parent.Parents +  .  + CAST(NextGeneration.GoalParentID AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM Goals AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.GoalParentID = Parent.GoalID    
)
SELECT * 
    FROM Hierarchy 
OPTION(MAXRECURSION 32767)

Can any one help me to write a function to create serial number in a hierarchical way

Answers

https://msdn.microsoft.com/en-us/library/ms186734.aspx https://msdn.microsoft.com/en-us/library/ms186734.aspx

DECLARE @Goals TABLE (GoalId INT, GoalParentID INT, Goalstatement VARCHAR(100))

INSERT @Goals VALUES 
(1, 0,  abc ),
(2, 0,  def ),
(3, 1,  acc ),
(4, 2,  efc ),
(5, 3,  dec ),
(6, 0,  efc ),
(7, 3,  jhg )

;WITH NumberedGoals(GoalId, GoalParentID, Goalstatement, GoalSequence) AS (
    SELECT
        GoalId, GoalParentID, Goalstatement, ROW_NUMBER() OVER (PARTITION BY GoalParentID ORDER BY GoalId) AS GoalSequence
    FROM
        @Goals
), Hierarchy(GoalID, GoalParentID, GoalSequence, Parents)
AS
(
    SELECT GoalID, GoalParentID, GoalSequence, CAST(GoalSequence AS VARCHAR(MAX))
        FROM NumberedGoals AS FirtGeneration
        WHERE GoalParentID = 0    
    UNION ALL
    SELECT NextGeneration.GoalID, NextGeneration.GoalParentID, NextGeneration.GoalSequence,
        CAST(CASE WHEN Parent.Parents =   
            THEN(CAST(NextGeneration.GoalSequence AS VARCHAR(MAX)))
            ELSE(Parent.Parents +  .  + CAST(NextGeneration.GoalSequence AS VARCHAR(MAX)))
        END AS VARCHAR(MAX))
        FROM NumberedGoals AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.GoalParentID = Parent.GoalID    
)
SELECT h.Parents as [Serial no], h.GoalId, h.GoalParentId, g.GoalStatement
    FROM Hierarchy h
    JOIN @Goals g ON g.GoalID = h.GoalID
OPTION (MAXRECURSION 32767)

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/28477407/how-to-generate-sequence-numbers-for-hierarchical-data-in-sql-server

Related

Outils personnels
Espaces de noms

Variantes
Actions
Navigation
Outils