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
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)
http://stackoverflow.com/questions/28477407/how-to-generate-sequence-numbers-for-hierarchical-data-in-sql-server