1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
| WITH CTE
AS
(
SELECT *
FROM (VALUES (1, 0, 'Button1', NULL, 0),
(2, 1, 'B1I1', 'template.aspx', 0),
(3, 1, 'B1I2', 'template.aspx', 0),
(4, 1, 'B1I3', NULL, 0),
(5, 4, 'B1I3I1', 'template.aspx', 0),
(6, 4, 'B1I3I1', 'template.aspx', 0),
(7, 0, 'Button2', NULL, 0),
(8, 7, 'B2I1', 'template.aspx', 0),
(9, 7, 'B2I2', 'template.aspx', 0),
(10, 7, 'B2I3', 'template.aspx', 0),
(11, 7, 'B2I4', NULL, 0),
(12, 11, 'B2I4I1', 'template.aspx', 0),
(13, 12, 'B2I4I1I1', 'template.aspx', 0)) AS T(ID, Parent, [Text], href, sort)
)
SELECT
1 AS Tag,
NULL AS Parent,
[text] AS "TopMenu!1!Text",
NULL AS "MenuItem!2!href",
NULL AS "MenuItem!2!Text",
NULL AS "MenuItem!3!href",
NULL AS "MenuItem!3!Text",
NULL AS "MenuItem!4!href",
NULL AS "MenuItem!4!Text"
FROM CTE
WHERE Parent = 0
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
CTE.[Text],
ItemMenu.href,
ItemMenu.[text],
NULL,
NULL,
NULL,
NULL
FROM CTE
INNER JOIN CTE AS ItemMenu
ON CTE.ID = ItemMenu.Parent
WHERE CTE.Parent = 0
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
CTE.[Text],
ItemMenu.href,
ItemMenu.[text],
ItemMenuSub.href,
ItemMenuSub.[Text],
NULL,
NULL
FROM CTE
INNER JOIN CTE AS ItemMenu
ON CTE.ID = ItemMenu.Parent
INNER JOIN CTE AS ItemMenuSub
ON ItemMenu.ID = ItemMenuSub.Parent
WHERE CTE.Parent = 0
UNION ALL
SELECT
4 AS Tag,
3 AS Parent,
CTE.[Text],
ItemMenu.href,
ItemMenu.[text],
ItemMenuSub.href,
ItemMenuSub.[Text],
ItemMenuSubSub.href,
ItemMenuSubSub.[Text]
FROM CTE
INNER JOIN CTE AS ItemMenu
ON CTE.ID = ItemMenu.Parent
INNER JOIN CTE AS ItemMenuSub
ON ItemMenu.ID = ItemMenuSub.Parent
INNER JOIN CTE AS ItemMenuSubSub
ON ItemMenuSub.ID = ItemMenuSubSub.Parent
WHERE CTE.Parent = 0
ORDER BY "TopMenu!1!Text", "MenuItem!2!Text", "MenuItem!3!Text", "MenuItem!4!Text"
FOR XML EXPLICIT, ROOT('RootMenu'); |
Partager