Recently I worked on a project that requires an unordered
list to be prepared dynamically depending on many filters. Users are
consistently generating the results with various filters and the results are
shown as a list.
Well there are many ways to do this. You can simply get the
results to client-side and construct the list using Javascript. But I thought
of constructing the list using SQL as I’m anyway required to query a database.
To demonstrate what I did, let’s create a self-referenced
table named “Menu” and add some data.
CREATE TABLE
[dbo].[Menu](
[MenuID]
[int] NOT NULL,
[ParentID]
[int],
[MenuName]
[varchar](50) NOT NULL,
CONSTRAINT
[PK_Menu] PRIMARY KEY
CLUSTERED
(
[MenuID]
ASC
))
ALTER TABLE
[dbo].[Menu]
WITH CHECK
ADD CONSTRAINT [FK_Menu_Menu] FOREIGN
KEY([ParentID])
REFERENCES [dbo].[Menu] ([MenuID])
INSERT INTO
Menu (MenuID, ParentID, MenuName)
VALUES (1, NULL, 'File'),
(2, 1, 'New'),
(3, 1, 'Open'),
(4, 3, 'Project'),
(5, 3, 'File'),
(6, 1, 'Save'),
(7, 1, 'Close'),
(8, NULL, 'Edit'),
(9, 8, 'Undo'),
(10,
8, 'Redo');
The data in the table and the final result are as follows:-
The logic here is to select the top layer of the menu first
and select corresponding menu items as inline list items. The code is as
follows:-
SELECT l1.MenuName
li,
(SELECT L2.MenuName li,
(SELECT L3.MenuName li
FROM Menu L3
WHERE L3.ParentID = L2.MenuID
FOR XML PATH (''), ROOT ('ul'), TYPE) li
FROM Menu L2
WHERE L2.ParentID = l1.MenuID
FOR XML PATH (''), ROOT ('ul'), TYPE) li
FROM Menu l1
WHERE l1.ParentID
IS NULL
FOR XML PATH ('ul');
The result is:-
<ul>
<li>
File<ul>
<li>New</li>
<li>
Open<ul>
<li>Project</li>
<li>File</li>
</ul>
</li>
<li>Save</li>
<li>Close</li>
</ul>
</li>
</ul>
<ul>
<li>
Edit<ul>
<li>Undo</li>
<li>Redo</li>
</ul>
</li>
</ul>
Note:-
0 comments:
Post a Comment