T-SQL行转列和计算

在T-SQL中，可以使用PIVOT和UNPIVOT操作符来实现行转列和计算的功能。下面是一个包含代码示例的解决方法：

1. 行转列（PIVOT）：

-- 创建一个示例表
CREATE TABLE Sales (
  ProductName VARCHAR(50),
  Year INT,
  Quarter INT,
  SalesAmount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO Sales (ProductName, Year, Quarter, SalesAmount)
VALUES ('ProductA', 2019, 1, 100),
       ('ProductA', 2019, 2, 200),
       ('ProductA', 2019, 3, 150),
       ('ProductA', 2019, 4, 300),
       ('ProductB', 2019, 1, 50),
       ('ProductB', 2019, 2, 100),
       ('ProductB', 2019, 3, 75),
       ('ProductB', 2019, 4, 150);

-- 使用PIVOT将行转列
SELECT ProductName, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4
FROM (
  SELECT ProductName, Quarter, SalesAmount
  FROM Sales
) AS SourceTable
PIVOT (
  SUM(SalesAmount)
  FOR Quarter IN ([1], [2], [3], [4])
) AS PivotTable;

输出结果：

2. 列转行（UNPIVOT）：

-- 使用UNPIVOT将列转行
SELECT ProductName, Quarter, SalesAmount
FROM (
  SELECT ProductName, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4
  FROM (
    SELECT ProductName, Quarter, SalesAmount
    FROM Sales
  ) AS SourceTable
  PIVOT (
    SUM(SalesAmount)
    FOR Quarter IN ([1], [2], [3], [4])
  ) AS PivotTable
) AS UnpivotTable;

输出结果：

3. 行转列和计算：

-- 使用PIVOT将行转列并计算总销售额
SELECT ProductName, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4, [1] + [2] + [3] + [4] AS TotalSales
FROM (
  SELECT ProductName, Quarter, SalesAmount
  FROM Sales
) AS SourceTable
PIVOT (
  SUM(SalesAmount)
  FOR Quarter IN ([1], [2], [3], [4])
) AS PivotTable;

输出结果：

以上是使用PIVOT和UNPIVOT操作符来实现行转列和计算的解决方法。根据实际需求，可以对示例代码进行修改和调整。