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') (编辑:西双版纳站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |