以 HTML 格式返回 Select 语句 (SQL 2005) [英] Return Select Statement as formatted HTML (SQL 2005)

问题描述

我有一个看起来像这样的数据集:

I have a data set that looks something like this:

   Gender | Age | Name
    Male  | 30  | Bill
  Female  | 27  | Jenny
  Female  | 27  | Debby 
   Male   | 44  | Frank

我试图将其显示为特殊格式的 HTML 代码:

And I'm trying to display this as specially formatted HTML code:

    <ul>
      <li>Male
        <ul>
          <li>30
            <ul>
              <li>Bill</li>
            </ul>
          </li>
          <li>44
            <ul>
              <li>Frank</li>
            </ul>
          </li>
        </ul>  
      </li>
    </ul>

    <ul>
      <li>Female
        <ul>
          <li>27
            <ul>
              <li>Jenny</li>
              <li>Debby</li>
            </ul>
          </li>
        </ul>  
      </li>
    </ul>

我尝试使用 FOR XML 但这并没有给出我想要的结果.它没有删除返回的多个 Gender 和 Age 字段.正如您在此 HTML 中看到的那样，它正在将所有内容组合在一起，并且仅在结束节点处提供重复项.

I tried using FOR XML but that didn't give the results I was looking for. It didn't remove the multiple Gender and Age fields returned. As you can see in this HTML it is compounding it all and only giving duplicates at the end node.

Aaron Bertrand 在这里提供了一个很好的方法将选择语句作为格式化的 HTML 返回这似乎在 SQL 2008 中完美运行，但我一直在寻找在 2005 中也能运行的东西，除了一些小东西，比如 += 运算符和设置默认的 DECLARE 值，只是不显示在2005.

Aaron Bertrand provided an excellent method here Return Select Statement as formatted HTML that seems to work perfectly in SQL 2008, but I was looking for something that worked in 2005 as well, besides the little stuff, like += operators and setting default DECLARE values, just doesn't display in 2005.

如何在 SQL Server 2005 中实现这样的功能?

How would something like this be achieved in SQL Server 2005?

推荐答案

declare @T table
(
  Gender varchar(10),
  Age int,
  Name varchar(10)
)

insert into @T values ('Male',   30,   'Bill')
insert into @T values ('Female', 27,   'Jenny')
insert into @T values ('Female', 27,   'Debby') 
insert into @T values ('Male',   44,   'Frank')

select Gender as 'li',
       (select T2.Age as 'li',
               (select T3.Name as 'li'
                from @T as T3
                where T2.Age = T3.Age and
                      T1.Gender = T3.Gender
                for xml path(''), root('ul'), type) as 'li'
        from @T as T2
        where T1.Gender = T2.Gender
        group by T2.Age
        for xml path(''), root('ul'), type) as 'li'
from @T as T1
group by Gender
for xml path('ul')

这篇关于以 HTML 格式返回 Select 语句 (SQL 2005)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！