/*
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCatID] [int] NOT NULL,
[CategoryName] [varchar](50) NOT NULL,


and data is in this form

2 5 Sub Category 
3 2 Tertiary Category 1 
4 2 Tertiary Category 2 
5 0 Category 1 
6 0 Category 2 

I want a select statement which shows data in this form: ( if there is parent category then it should be appendeded no matter how many parents)

Category
Category / Sub Category
Category / Sub Category / Tertiary Category
Category / Sub Category 
*/
DECLARE @foo TABLE (
	[categoryID] INT PRIMARY KEY
	, [parentCatID] INT
	, [categoryName] VARCHAR(50)
	)

INSERT @foo ([categoryID], [parentCatID], [categoryName])
      SELECT 2, 5, 'Sub Cetegory'
UNION SELECT 3, 2, 'Tertiary Category 1'
UNION SELECT 4, 2, 'Tertiary Category 2'
UNION SELECT 5, 0, 'Category 1'
UNION SELECT 6, 0, 'Category 2'

; WITH

	tree ([categoryID], [categoryName], [path])
	AS (

	-- Anchor Definition
	SELECT
		[categoryID], [categoryName], CAST([categoryName] AS VARCHAR(MAX))
	FROM
		@foo
	WHERE
		[parentCatId] = 0

	-- Recursive Definition
	UNION ALL SELECT
		f.[categoryID], f.[categoryName], t.[path] + ' / ' + CAST(f.[categoryName] AS VARCHAR(MAX))
	FROM
		@foo AS f
		JOIN tree AS t ON t.[categoryID] = f.[parentCatID]
	)

SELECT * FROM tree ORDER BY [path]