sql - 创建嵌套 html 列表的 cte

Question

我有以下 cte:

WITH cte AS
(
        SELECT 
            c.ParentIDNo,  
            c.Category,
            c.ChildCategory,
            c.WSWebProductNameIDNo, 
            c.IDNo, 
            0 AS Level,
            CAST('/' + c.Category AS VARCHAR(1000)) as CteName
        FROM WSWebCategory as c
        WHERE c.ParentIDNo IS NULL

        UNION ALL

        SELECT   
            t.ParentIDNo, 
            t.Category,
            t.ChildCategory, 
            t.WSWebProductNameIDNo,
            t.IDNo, 
            cte.Level + 1 AS Level,
            CAST(cte.CteName + '/' + t.ChildCategory AS VARCHAR(1000)) AS CteName
        FROM WSWebCategory t
        INNER JOIN cte ON t.ParentIDNo = cte.IDNo
    )
    SELECT *, REPLICATE('----', Level) + CteName as CteName FROM cte
    ORDER BY cte.CteName

这给了我一个像这样的列表:

    /Apparatus    ----/Apparatus/Autoclaves    --------/Apparatus/Autoclaves/ALL-AMERICAN Portable Sterilizers (WAFCO)    --------/Apparatus/Autoclaves/Autoclave Accessories    ------------/Apparatus/Autoclaves/Autoclave Accessories/Clavies® Autoclave Gloves (Bel-Art Scienceware)    ------------/Apparatus/Autoclaves/Autoclave Accessories/Grabbit™ Temp Mitts (Heathrow Scientific)    ------------/Apparatus/Autoclaves/Autoclave Accessories/Odo-Clave® Deodorant Pads (Bel-Art Scienceware)    --------/Apparatus/Autoclaves/Autoclave Bags    ------------/Apparatus/Autoclaves/Autoclave Bags/Autoclavable Biohazard Disposal Bags (Bel-Art Scienceware)    ------------/Apparatus/Autoclaves/Autoclave Bags/Autoclavable Biohazard Disposal Bags (Gosselin)    ------------/Apparatus/Autoclaves/Autoclave Bags/Autoclavable Hi-Temp Biohazard Disposal Bags (Bel-Art Scienceware)    ------------/Apparatus/Autoclaves/Autoclave Bags/Chex-All® II Sterilization Pouches (Propper)    ------------/Apparatus/Autoclaves/Autoclave Bags/Clavies® Autoclave Gloves (Bel-Art Scienceware)    ------------/Apparatus/Autoclaves/Autoclave Bags/Clavies® Bag Holder (Bel-Art Scienceware)    ------------/Apparatus/Autoclaves/Autoclave Bags/Clavies® General Purpose Autoclavable Bags (Bel-Art Scienceware)    ------------/Apparatus/Autoclaves/Autoclave Bags/Poxygrid® Biohazard Bag Holder (Bel-Art Scienceware)

but what I'm after is a set of nested html lists:

<ul> 
<li>Apparatus
   <ul> 
    <li>Autoclaves
        <ul>   
            <li>ALL-AMERICAN Portable Sterilizers (WAFCO)</li> 
            <li>Autoclave Accessories
                <ul>
                    <li>Clavies® Autoclave Gloves (Bel-Art Scienceware)</li>
                    <li>Grabbit™ Temp Mitts (Heathrow Scientific)</li>
                    <li>Odo-Clave® Deodorant Pads (Bel-Art Scienceware) </li>
                </ul>
            </li>
            <li>Autoclave Bags
                <ul>
                    <li>Autoclavable Biohazard Disposal Bags (Bel-Art Scienceware)</li>
                    <li>Autoclavable Biohazard Disposal Bags (Gosselin)</li>
                    <li>Autoclavable Hi-Temp Biohazard Disposal Bags (Bel-Art Scienceware)</li>
                    <li>Chex-All® II Sterilization Pouches (Propper)</li>
                    <li>Clavies® Autoclave Gloves (Bel-Art Scienceware)</li>
                    <li>Clavies® Bag Holder (Bel-Art Scienceware)</li>
                    <li>Clavies® General Purpose Autoclavable Bags (Bel-Art Scienceware)</li>
                    <li>Poxygrid® Biohazard Bag Holder (Bel-Art Scienceware)</li>
                </ul>
            </li>
        </ul>
    </li> 
    </ul>
</li>
</ul> 

我不知道该怎么做!

Answer 1

有什么原因让您不能使用更高级别的编程语言在背面解析它吗？

看起来您的数据是 XML 输出的极佳候选者(使用 SELECT ... FOR XML EXPLICIT。)您的数据似乎为此完美设置，应用程序端的某些东西很容易解析它。

除此之外，您可能会使用结果的串联来添加列表项标签，​​并在下一个 CTE 循环中剥离标签，然后再阅读扩展文本。不过，结束标记将是一个挑战，因为我不相信 CTE 会以递归方式退出以允许您关闭它们。