I have a table with contracts stored as XML. Using the XML query possibilities in SQL Server 2005 together with the UNPIVOT keyword I can get statistics on different types of contracts.
My table has these columns:
ContractNumber int (Primary Key)
contractXML xml
My XML looks something like like this, and is stored in a column of type XML:
<Contract>
<Customer>
...
</Customer>
<Order>
<OrderItem>
<Name>Product1</Name>
<Price>123.00</Price>
</OrderItem>
<OrderItem>
<Name>Product1</Name>
<Price>123.00</Price>
</OrderItem>
<OrderItem>
<Name>Product3</Name>
<Price>13.00</Price>
</OrderItem>
<OrderItem>
<Name>Product2</Name>
<Price>23.00</Price>
</OrderItem>
<OrderItem>
<Name>Product1</Name>
<Price>123.00</Price>
</OrderItem>
</Order>
</Contract>
To get a table with a max of 5 OrderItems as columns, I can use this query:
SELECT
ContractNumber,
contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)') AS Product1,
contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)') AS Product2,
contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)') AS Product3,
contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)') AS Product4,
contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)') AS Product5
FROM Contract
This is nice, but what if I want to get the number of each product sold? The answer is that I can use the UNPIVOT operator!
Something like this will do it:
SELECT Name, COUNT(*) AS [Count] FROM
(
SELECT
ContractNumber, col, Name
FROM
(SELECT
ContractNumber,
[col1] = contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)'),
[col2] = contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)'),
[col3] = contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)'),
[col4] = contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)'),
[col5] = contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)')
FROM Contract) col
UNPIVOT(
Name
FOR col
IN ([col1],[col2],[col3],[col4],[col5])
) AS unpvt
) AS T
GROUP BY PackageName
The UNPIVOT operator gives the values in the 5 columns as 1 column.
If I had only the 1 row in my Contract table from the example above, the result would be:
| Name | Count |
| Product1 | 3 |
| Product2 | 1 |
| Product3 | 1 |
This is a simple example, and it has a max number of ordered products per contract of 5. Could maybe be extended.
If you want a table with the counts for the different products as columns, then something like this would do the job:
SELECT
SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract
This would give this result:
| Product1 | Product2 | Product3 |
| 3 | 1 | 1 |
And we could of course UNPIVOT this result too:
SELECT
ProcuctCount
FROM
(SELECT
SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract) cols
UNPIVOT(
ProductCount
FOR cols IN (Product1, Product2, Product3)
) AS unpvt
This should give a table like this:
| ProductCount |
| 3 |
| 1 |
| 1 |
