使用 XML 列的 SQL Pivot [英] SQL Pivot using an XML column

问题描述

有没有人有一个使用包含 XML 列的表的数据透视示例 - 特别是将 xml 列中的某些元素转换为数据透视表中的列?我正在尝试构建一个，但语法正在杀死我；与 C# 中的 xml 相比，sql 中的 xml 很难使用.我怀疑这是不可能的，但检查一下也无妨:)

Does anyone have an example of a pivot using a table with an XML column in it - specifically turning some of the elements in the xml column into a column in the pivot table? I'm trying to build one, but the syntax is killing me; xml in sql is damn hard to work with, compared to xml in C#. I suspect this is impossible, but it wouldn't hurt to check :)

推荐答案

这里是一个简单的示例，其中包含存储在 xml 中的产品详细信息的商店.枢轴位于底部，显示商店和 A 类和 B 类的价格总和.

Here is a simple example that has Stores with Product details stored in xml. The pivot is at the bottom which shows the stores and a sum of price by categories A and B.

declare @test Table
(

    StoreID int,
    ProdXml xml
)

insert into @test
select 1, '<product cat="A" name="foo" price="10" />' union
select 2, '<product cat="A" name="bar" price="12" />' union
select 1, '<product cat="B" name="blah" price="35" />' union    
select 2, '<product cat="B" name="bap" price="67" />' union
select 1, '<product cat="C" name="bip" price="18" />' union
select 2, '<product cat="A" name="bing" price="88" />' union
select 1, '<product cat="B" name="bang" price="34" />' union    
select 2, '<product cat="B" name="boom" price="65" />' 

--Pivot showing sum of price by Cat
select  StoreID, A, B
from
(   
    select  StoreID,
        ProdXml.value('/product[1]/@cat[1]','varchar(20)') as [ProdCat],
        ProdXml.value('/product[1]/@price[1]','int') as [ProdPrice]
    from  
        @test
) up
PIVOT (SUM([ProdPrice]) FOR [ProdCat] IN ( A, B)) as pvt
ORDER BY StoreID

这篇关于使用 XML 列的 SQL Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！