SQL Server:带有 XML 输出的两级 GROUP BY [英] SQL Server: Two-level GROUP BY with XML output
本文介绍了SQL Server:带有 XML 输出的两级 GROUP BY的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个分层数据表,我试图将其选择为单个分组的 XML 值:
I have a table of hierarchical data that I am trying to select as a single, grouped XML value:
列: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:
For the above data, I would like to output the following 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) 进一步分组.
i.e. a single xml structure containing all rows from the above table, grouped by the first column (Type), and further grouped by the second column (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, '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')
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')
这篇关于SQL Server:带有 XML 输出的两级 GROUP BY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文