Is it possible to convert the rows to columns then sum the amount according to specific values witho-CodePudding

Is it possible to convert the rows to columns and find the sum of Amount for the laptop and desktop for each customers and store them in separate columns of laptop and desktop without using pivot function

table creation:

CREATE TABLE Customers
(
       CustomerName VARCHAR(50),
    ProductName VARCHAR(50),
    Amount INT
);

INSERT INTO Customers VALUES('James', 'Laptop', 30000);
INSERT INTO Customers VALUES('James', 'Desktop', 25000);
INSERT INTO Customers VALUES('David', 'Laptop', 25000);
INSERT INTO Customers VALUES('Smith', 'Desktop', 30000);
INSERT INTO Customers VALUES('Pam', 'Laptop', 45000);
INSERT INTO Customers VALUES('Pam', 'Laptop', 30000);
INSERT INTO Customers VALUES('John', 'Desktop', 30000);
INSERT INTO Customers VALUES('John', 'Desktop', 30000);
INSERT INTO Customers VALUES('John', 'Laptop', 30000);

customers output:

CustomerName   ProductName  Amount
James          Laptop       30000
James          Desktop      25000
David          Laptop       25000
Smith          Desktop      30000
Pam            Laptop       45000
Pam            Laptop       30000
John           Desktop      30000
John           Desktop      30000
John           Laptop       30000

Desired Output:

CustomerName    Laptop  Desktop
David           25000   null
James           30000   25000
John            30000   60000
Pam             75000   null
Smith           null    30000

CodePudding user response：

With known ProductName values, it can query with CASE WHEN

SELECT CustomerName, 
SUM(CASE WHEN ProductName = 'Laptop' THEN Amount ELSE 0 END) Laptop,
SUM(CASE WHEN ProductName = 'Desktop' THEN Amount ELSE 0 END) Desktop
FROM Customers 
GROUP BY CustomerName