How to get parent id as below

Transact-SQL
1

Hi I have a table like below
id tree
1 1
2 1.1
3 1.1.1
4 1.2
5 1.2.1
6 1.2.2
7 1.2.2.1
8 1.2.2.2
9 1.3
10 1.3.1
11 1.3.2
12 1.3.3
13 1.3.3.1
14 1.3.3.2
i need the a column pid as shown below
id pid tree
1 0 1
2 1 1.1
3 2 1.1.1
4 3 1.2
5 4 1.2.1
6 4 1.2.2
7 6 1.2.2.1
8 6 1.2.2.2
9 8 1.3
10 9 1.3.1
11 9 1.3.2
12 9 1.3.3
13 12 1.3.3.1
14 12 1.3.3.2

Can somebody help me please...Thanks in advance

2

In future, please provide consumable test data:

CREATE TABLE #t
(
	id int NOT NULL PRIMARY KEY
	,tree varchar(50) NOT NULL
);
INSERT INTO #t
VALUES (1, '1')
	,(2, '1.1')
	,(3, '1.1.1')
	,(4, '1.2')
	,(5, '1.2.1')
	,(6, '1.2.2')
	,(7, '1.2.2.1')
	,(8, '1.2.2.2')
	,(9, '1.3')
	,(10, '1.3.1')
	,(11, '1.3.2')
	,(12, '1.3.3')
	,(13, '1.3.3.1')
	,(14, '1.3.3.2');

Try:

WITH pTrees
AS
(
	SELECT T.id, T.tree
		,REVERSE(SUBSTRING(X.rTree, CHARINDEX('.', X.rTree) + 1, 50)) AS pTree
	FROM #t T
		CROSS APPLY
		(
			VALUES(REVERSE(T.tree))
		) X (rTree)
)
SELECT T.id
	,CASE
		WHEN T.id = P.id
		THEN 0
		ELSE P.id
	END AS pid
	,T.tree
FROM pTrees T
	JOIN #t P
		ON T.pTree = P.tree;
1 Like
3

Sure....Next time i will give the test data......Thank you very much...it worked.... :smiley:

4

hi

i know its been solved ..

Here is a very short and simple way to do it

Hope this helps :slight_smile:

SELECT
    [#t].[id]
    , ROW_NUMBER() OVER (ORDER BY
                             CAST('/' + [#t].[tree] + '/' AS HIERARCHYID)) - 1 AS [PID]
    , [#t].[tree]
FROM
    [#t];

image

5

If you are going to use hierarchyid you need to use GetAncestor:

WITH Hierarchy
AS
(
	SELECT T.id, T.tree, X.hid
		,X.hid.GetAncestor(1) AS phid
	FROM #t T
		CROSS APPLY
		(
			VALUES(CAST('/' + REPLACE(T.tree,'.','/') + '/' AS hierarchyid))
		) X (hid)
)
SELECT H.id, COALESCE(P.id, 0) AS pid, H.tree
FROM Hierarchy H
	LEFT JOIN Hierarchy P
		ON H.phid = P.hid;