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 :)
1
-
I got a downvote on this one and I can't for the life of me think why.jcollum– jcollum2009-01-21 01:50:41 +00:00Commented Jan 21, 2009 at 1:50
Add a comment
|
1 Answer
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
