SQL Server 语法问题。结合 Pivot、XML 解析和 JOIN

使用SQL Server进行Pivot、XML解析和JOIN的语法问题

sql
-- 创建示例表
CREATE TABLE Sales (
    ProductID INT,
    SalesDate DATE,
    Quantity INT
);
-- 插入示例数据
INSERT INTO Sales VALUES (1, '2023-01-01', 10);
INSERT INTO Sales VALUES (1, '2023-01-02', 15);
INSERT INTO Sales VALUES (2, '2023-01-01', 20);
INSERT INTO Sales VALUES (2, '2023-01-02', 25);

sql
-- 使用Pivot进行数据转置
SELECT *
FROM (
    SELECT ProductID, SalesDate, Quantity
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Quantity)
    FOR SalesDate IN ([2023-01-01], [2023-01-02])
) AS PivotTable;

sql
-- 使用XML解析实现动态列
DECLARE @Columns NVARCHAR(MAX);
DECLARE @Query NVARCHAR(MAX);
-- 生成列名
SELECT @Columns = COALESCE(@Columns + ', ', '') + QUOTENAME(SalesDate)
FROM (SELECT DISTINCT SalesDate FROM Sales) AS Dates;
-- 构建动态SQL查询语句
SET @Query = '
    SELECT *
    FROM (
        SELECT ProductID, SalesDate, Quantity
        FROM Sales
    ) AS SourceTable
    PIVOT (
        SUM(Quantity)
        FOR SalesDate IN (' + @Columns + ')
    ) AS PivotTable;';
-- 执行动态SQL查询
EXEC sp_executesql @Query;

sql
-- 创建产品信息表
CREATE TABLE Product (
    ProductID INT,
    ProductName VARCHAR(50)
);
-- 插入示例数据
INSERT INTO Product VALUES (1, 'ProductA');
INSERT INTO Product VALUES (2, 'ProductB');
-- 使用JOIN关联Sales表和Product表
SELECT P.ProductName, PT.*
FROM (
    SELECT *
    FROM (
        SELECT ProductID, SalesDate, Quantity
        FROM Sales
    ) AS SourceTable
    PIVOT (
        SUM(Quantity)
        FOR SalesDate IN (' + @Columns + ')
    ) AS PivotTable
) AS PT
JOIN Product AS P ON PT.ProductID = P.ProductID;