用pivot xml写个sql示例
时间: 2023-07-01 09:07:34 浏览: 51
假设有两个表,一个是订单表,一个是订单明细表,它们之间的关系是一对多,即一个订单可以对应多个订单明细。
订单表结构:
```sql
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE
);
```
订单明细表结构:
```sql
CREATE TABLE order_details (
order_detail_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
price DECIMAL(10,2)
);
```
现在我们想要查询每个客户的订单数量和订单总金额,可以使用 Pivot XML 将订单明细按客户进行分组聚合,然后再与订单表连接得到每个客户的订单数量和订单总金额。
示例查询语句如下:
```sql
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(price * quantity) AS total_amount,
CAST(
(SELECT
product_id AS [id],
quantity AS [quantity],
price AS [price]
FROM order_details od
WHERE od.order_id = o.order_id
FOR XML PATH('product'), ROOT('products'), TYPE
) AS NVARCHAR(MAX)
) AS products
FROM
orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY
customer_id
```
以上查询语句中,使用 Pivot XML 的部分是在子查询中,将订单明细表按客户进行分组聚合,并将每个客户的订单明细转换为 XML 格式的字符串,然后再将该字符串转换为 NVARCHAR(MAX) 类型的数据。这样就可以在主查询中将每个客户的订单明细作为一个 XML 字符串返回。