Let's come with an example.
Example:-
1) First here I create one function
CREATE FUNCTION CategoryTitle
(
@ID int
) RETURNS varchar(255)
AS
BEGIN
DECLARE @Name VARCHAR(255)
SELECT @Name=Name from CategoryMaster WHERE ID=@ID
return @Name
END
2) Then now I will create one table say as 'CategoryMaster' and inserted some proper values in it.
CREATE Table CategoryMaster
(
ID INT identity(1,1)
, Name varchar(255)
, ParentId int
)
INSERT INTO CategoryMaster VALUES ('Electronics',0),('Camera',1),('Mobile',1),('Sony Ericsson',3),('Nokia',3)
3) Now show the result of CategoryMaster table.
SET NOCOUNT ON
SELECT ID,Name,ParentId FROM CategoryMaster
SELECT ID,[Name] AS Category ,ISNULL((dbo.CategoryTitle(ParentId)),'Root') AS [Parent Category] FROM CategoryMaster
Other way using CTE:-
;WITH Category AS
(
SELECT ID,Name AS Category,CAST('Root' AS VARCHAR) AS [Parent Category] FROM CategoryMaster WHERE ParentId=0
UNION ALL
SELECT CM.ID,CM.Name AS Category,CAST(C.Category AS VARCHAR) AS [Parent Category] FROM CategoryMaster CM
INNER JOIN Category C ON C.ID=CM.ParentId
)
SELECT * FROM Category
RESULT :-
I hopes this will help you.
