加入收藏 | 设为首页 | 会员中心 | 我要投稿 西双版纳站长网 (https://www.0691zz.com.cn/)- 数据计算、IT业界、服务器、教程、云日志!
当前位置: 首页 > 站长学院 > MsSql教程 > 正文

SQL Server:带XML输出的两级GROUP BY

发布时间:2021-01-14 06:11:16 所属栏目:MsSql教程 来源:网络整理
导读:我有一个分层数据表,我试图选择作为单个分组的XML值: 列:Id,Type,SubType,SubSubType 样本数据: Id Type Subtype SubSubType1 Product Documentation Brochures Functional Brochures2 Product Documentation Brochures Fliers3 Product Documentation Dat

我有一个分层数据表,我试图选择作为单个分组的XML值:

列:Id,Type,SubType,SubSubType

样本数据:

Id  Type                    Subtype                    SubSubType
1   Product Documentation   Brochures                  Functional Brochures
2   Product Documentation   Brochures                  Fliers
3   Product Documentation   Data Sheets and Catalogs   Data Sheets
4   Product Documentation   Data Sheets and Catalogs   Catalogs
5   Other Documentation     Other classification       User Guides

对于上面的数据,我想输出以下xml:

<AllTypes>
    <Type name="Product Documentation">
        <SubType name="Brochures">
            <SubSubType name="Functional Brochures"/>
            <SubSubType name="Fliers"/>
        </SubType>
        <SubType name="Data Sheets and Catalogs">
            <SubSubType name="Data Sheets"/>
            <SubSubType name="Catalogs"/>
        </SubType>
    </Type>
    <Type name="Other Documentation">
        <SubType name="Other classification">
            <SubSubType name="User Guides"/>
        </SubType>
    </Type>
</AllTypes>

即包含上表中所有行的单个xml结构,按第一列(Type)分组,并进一步按第二列(SubType)分组.

解决方法

declare @T table
(
  ID int,Type varchar(30),SubType varchar(30),SubSubType varchar(30)
)

insert into @T values
(1,'Product Documentation','Brochures','Functional Brochures'),(2,'Fliers'),(3,'Data Sheets and Catalogs','Data Sheets'),(4,'Catalogs'),(5,'Other Documentation','Other classification','User Guides')

select T1.Type as '@Name',(
       select T2.SubType as '@Name',(
              select T3.SubSubType as '@Name'
              from @T as T3
              where T3.SubType = T2.SubType and
                    T3.Type = T1.Type
              for xml path('SubSubType'),type
              )
       from @T as T2
       where T2.Type = T1.Type
       group by T2.SubType
       for xml path('SubType'),type
       )
from @T as T1
group by Type
for xml path('Type'),root('AllTypes')

(编辑:西双版纳站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读