List of Category with it's parent category name

Here I post one example which will show the result of category and it's parent category name.
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.