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

我有一个分层数据表,我试图选择作为单个分组的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')

（编辑：徐州站长网）

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