Click here to Skip to main content
14,699,089 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 21-Dec-13 23:28pm
v2

1 solution

Hello.
Try this one.
WITH categories (PSId, PSName, ParentId)
  AS (SELECT PSId, CAST(PSName AS VARCHAR(255)), 0
        FROM PSCategories AS pm
       WHERE pm.ParentId IS NULL
       UNION ALL
      SELECT ps.PSId, CAST(m.PSName + ' > ' + ps.PSName AS VARCHAR(255)), m.PSId
        FROM PSCategories AS ps
        JOIN categories AS m
          ON m.PSId = ps.ParentId
       WHERE ps.ParentId IS NOT NULL
       )
SELECT * FROM categories
ORDER BY PSId
   
Comments
amit Baswa 24-Dec-13 10:31am
   
HatsOff buddy... Thanks a lot.
Save my day... :)
skydger 24-Dec-13 13:11pm
   
You are welcome :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900