Click here to Skip to main content
14,449,906 members
Rate this:
Please Sign up or sign in to vote.
See more:
help me to achieve this result

Output has only 2 columns 
1. Text: Displayed in image
2. Id: ID of the last subject (last child)


OUTPUT:
Name Id
Accountancy > Payroll > Sage Payroll 5

It is just a sample

What I have tried:

CREATE TABLE Subjects
(
SubjectId INT PRIMARY KEY IDENTITY(1,1),
Subject VARCHAR(70),
ParentSubjectId INT,
IsActive BIT,
CreatedDate DATETIME,
CreatedBy INT,
UpdatedDate DATETIME,
UpdatedBy INT
);



INSERT INTO Subjects (Subject, ParentSubjectId) VALUES ('Accountancy',0);

INSERT INTO Subjects (Subject, ParentSubjectId) VALUES ('Accounting',1);
INSERT INTO Subjects (Subject, ParentSubjectId) VALUES ('Bookkipping',1);
INSERT INTO Subjects (Subject, ParentSubjectId) VALUES ('Payroll',1);

INSERT INTO Subjects (Subject, ParentSubjectId) VALUES ('Sage Payroll',4);



  SELECT * FROM Subjects
Posted
Updated 12-Feb-20 2:15am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You can use CTE[^] for such of requirement. See:
DECLARE @Subjects TABLE
(
	SubjectId INT PRIMARY KEY IDENTITY(1,1),
	SubjectText VARCHAR(70),
	ParentSubjectId INT
);

INSERT INTO @Subjects (SubjectText, ParentSubjectId)
VALUES ('Accountancy',0),('Accounting',1),
('Bookkipping',1),('Payroll',1),
('Sage Payroll',4)

--find the highset value of ParentSubjectId
DECLARE @lastid INT
SELECT @lastid = MAX(ParentSubjectId) FROM @Subjects
--get corresponding SubjectId
SELECT @lastid = MAX(SubjectId) FROM @Subjects WHERE ParentSubjectId = @lastid

--get data
;WITH CTE AS
(
	--initial query
	SELECT SubjectId, SubjectText, ParentSubjectId, CAST(SubjectText + ' > ' AS NVARCHAR(MAX)) AS LongSubject, 1 As LoopNo
	FROM @Subjects
	WHERE ParentSubjectId = 0
	--recursive part
	UNION ALL
	SELECT s.SubjectId, s.SubjectText, s.ParentSubjectId, LongSubject + s.SubjectText + ' > ' AS LongSubject, LoopNo + 1 As LoopNo
	FROM CTE AS c
		INNER JOIN @Subjects As s ON c.SubjectId = s.ParentSubjectId
)
SELECT LongSubject, SubjectId, LoopNo AS DataLevel
FROM CTE
WHERE SubjectId = @lastid


Result:
LongSubject								SubjectId	DataLevel
Accountancy > Payroll > Sage Payroll > 		5		3


Feel free to improve above code to your needs.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100