Category list with bread crumb style in MS SQLSERVER, like Opencart

See more:

SQL-Server

MySQL

Hi,

I have table structure in MS SQL SERVER as following

Table Name: PSCategories

PSId      | PSName         | ParentId(FK to PSCategories(PSId) )
1         | test1          | NULL
2         | test2          | 1
3         | test3          | NULL
4         | test4          | 3
5         | test5          | 4

I want the output of above table to be show as following

PSId     | PSName                  | ParentId
1        | test1                   | 0
2        | test1 > test2           | 1
3        | test3                   | 0
4        | test3 > test4           | 3
5        | test3 > test4 > test5   | 4

How can I achieve this using query in SQL SERVER?

---------------------------------------------------------------------

The similar type of result I found in Open-cart ADMIN system using following query in MY SQL

SELECT 
	cp.category_id AS category_id, 
	GROUP_CONCAT(c.name ORDER BY cp.level SEPARATOR ' > ') AS name, 
	c.parent_id, 
	c.sort_order 
FROM 
	oc_category_path cp LEFT JOIN oc_category c 
	ON (cp.path_id = c.category_id)  
GROUP BY 
	cp.category_id 
ORDER BY cp.category_id

The table structure there is as follow

Table: oc_Category
Category_Id |  Name | ParentId (FK to oc_Category(Category_Id))

Table: oc_category_path 
Category_Id |  PathId(FK to oc_Category(Category_Id))

As my table structure is not similar but some what same, and need to achieve using 1 table only, please help me generating query for MS SQLSERVER.

Thanks.