Convert multiple rows into one row with multiple columns in T-SQL

Question

I understand that there is this command called PIVOT and that's what I probably need but first time trying to use it, seems like I can't get its syntax to work.

So here is my sample data:

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')

I want those Project Name turn into columns so for example having columns like "Cleaning" , "Carpeting" , etc and then value of each column is the Price.

This is what I tried so far but it is wrong syntax:

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

John Cappelletti · Accepted Answer · 2021-10-18 18:04:48Z

1

Bracket [] your column names

Example or dbFiddle

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

answered Oct 18, 2021 at 18:04

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert multiple rows into one row with multiple columns in T-SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related