sql - 在 T-SQL 中将多行转换为具有多列的一行

Question

我知道有这个命令被调用PIVOT，这就是我可能需要的，但第一次尝试使用它，似乎我无法让它的语法工作。

所以这是我的示例数据：

CREATE TABLE MyTable 
(
    CompanyName NVARCHAR(20),
    Price INT,
    Project NVARCHAR(50)
);

INSERT INTO dbo.MyTable (CompanyName, Price, Project)
VALUES ('Dell', 450, 'Cleaning'),
       ('Dell', 150, 'Vaccuming'),
       ('Dell', 1200, 'Painting'),
       ('Dell', 100, 'VendingMachines'),
       ('Dell', 600, 'Wallpapers'),
       ('Dell', 820, 'Carpeting')

我希望将它们Project Name变成列，例如具有 "Cleaning" 、 "Carpeting" 等列，然后每列的值是Price.

这是我迄今为止尝试过的，但它是错误的语法：

SELECT *
FROM
    (SELECT CompanyName, Price, Project
     FROM dbo.MyTable) src
PIVOT
    (SUM(Price)
        FOR Project IN ('Cleaning', 'Vaccuming', 'Painting', 'VendingMachines', 'Wallpapers', 'Carpeting')
    ) piv;

标签： sqlsql-servertsqlpivot

Answer 1

括号[]你的列名

示例或dbFiddle

SELECT *
FROM
(
  SELECT  CompanyName, Price, Project
  FROM dbo.MyTable
) src
PIVOT
(
  SUM(Price)
  FOR Project IN ([Cleaning], [Vaccuming], [Painting], [VendingMachines], [Wallpapers], [Carpeting])
) piv;