If you want to show records (Parent Child concatenation), then following SQL Server recursive query could help you in achieve. For a clear picture have a look at following snap
Recursive Query showing only child Records
with parentChildResult as
(
select ItemId,
ItemDescription,
ItemCategoryId,
cast('' as nvarchar(max)) as ParentNames
from Items
where ItemCategoryId is null
union all
select i2.ItemId,
i2.ItemDescription,
i2.ItemCategoryId,
parentChildResult.ParentNames + ' > ' + parentChildResult.ItemDescription
from Items as i2
inner join parentChildResult
on parentChildResult.ItemId = i2.ItemCategoryId
)
select ItemId,
stuff(ParentNames, 1, 3, '') + ' > ' + ItemDescription as ParentNames
from parentChildResult
where ItemId in
(
SELECT i.ItemId FROM Items i
WHERE NOT EXISTS(SELECT 1 FROM Items I2 WHERE i.ItemId = I2.ItemCategoryId)
)
order by ParentNames
Recursive Query showing All parents and Child Records
with parentChildResult as
(
select ItemId,
ItemDescription,
ItemCategoryId,
cast('' as nvarchar(max)) as ParentNames
from Items
where ItemCategoryId is null
union all
select i2.ItemId,
i2.ItemDescription,
i2.ItemCategoryId,
parentChildResult.ParentNames + ' > ' + parentChildResult.ItemDescription
from Items as i2
inner join parentChildResult
on parentChildResult.ItemId = i2.ItemCategoryId
)
select ItemId,
stuff(ParentNames, 1, 3, '') + ' > ' + ItemDescription as ParentNames
from parentChildResult
order by ParentNames