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.