Database

Ali Al Omairi(Abu AlHassan) wrote:

i can't tie a user hands.

CREATE TABLE #data
(
	ID INT NOT NULL,
	PID INT NULL,
	VALUE VARCHAR(20) NOT NULL
);

INSERT INTO #data
(ID, PID, VALUE)
VALUES
(1, NULL, 'node 1'),
(4, 1, 'node 1/1'),
(6, 4, 'node 1/1/1'),
(5, 1, 'node 1/2'),
(7, 1, 'node 1/3'),
(2, NULL, 'node 2'),
(3, NULL, 'node 3'),
(8, 3, 'node 3/1');

WITH MyCTE AS
(
	SELECT ID, PID, VALUE, 0 AS [LEVEL]
	FROM #data
	WHERE PID IS NULL
	UNION ALL
	SELECT D.ID, D.PID, D.VALUE, [LEVEL] + 1
	FROM MyCTE M
		JOIN #data D
			ON D.PID = M.ID
)
SELECT ID, PID, VALUE, [LEVEL]
FROM MyCTE;

DROP TABLE #data;

Klaus-Werner Konrad wrote:

that confused me

Select House, 'Sale', Sale as Value
from Table
union
Select House, 'Purchase', Purchase as Value
from Table