I have a following table.I was trying to create a mega menu using single stored procedure.
CategoryId Category_name ParentId displayorder
1 Electronics 0 1
2 Vehicles 0 1
3 Computer 1 2
4 Camera 1 2
5 Mobile 1 2
6 Car 1 2
i am using the query below to get a three column table.
;WITH Hierarchy AS
(
SELECT Category_ID, Category_Name, Parent_ID,display_Order,show_Inmenu,Display_level
FROM #tbl_Category
WHERE Parent_ID = 0
UNION ALL
SELECT t.Category_ID, t.Category_Name, t.Parent_ID,t.display_Order,t.show_Inmenu,t.Display_level
FROM Hierarchy h
INNER JOIN #tbl_Category t ON t.Parent_ID = h.Category_ID
)
SELECT top 5 c.Category_ID,c.Category_Name,
'<div>' + (Select '<a href="index.aspx?id=' + cast(d.Category_ID as varchar(5)) +'">' +
d.Category_Name AS 'ul'
From Hierarchy d
Where d.parent开发者_开发问答_id = c.Category_ID
ORDER BY d.Category_Name
for xml path('')) + '</div>' as items
FROM Hierarchy c WHERE c.Parent_ID = 0 and c.show_Inmenu=1 order by c.Display_order
but the data getting from the select statement is
how can i remove < and >
from the items column.
is there any method to create table as below.
精彩评论