PIVOT rotates data being displayed in rows to columns.

Prepare data

Run the script below to create the Donation table with sample data:

CREATE TABLE [dbo].[Donation](
	[DonationID] [int] NULL,
	[DonationDate] [date] NULL,
	[DonationMethod] [varchar](20) NULL,
	[DonorName] [varchar](100) NULL,
	[DonorCity] [varchar](100) NULL,
	[Amount] [money] NULL
)

INSERT INTO Donation VALUES(1111, '01-01-2021', 'Cash', 'Adam Smith', 'New York', 100.00);
INSERT INTO Donation VALUES(1112, '01-01-2021', 'Credit Card', 'Brian Wilson', 'New York', 200.00);
INSERT INTO Donation VALUES(1113, '02-02-2021', 'Cash', 'Martin Ferguson', 'New York', 300);
INSERT INTO Donation VALUES(1114, '03-03-2021', 'Cash', 'Cris Ronoaldo', 'Chicago', 400.00);
INSERT INTO Donation VALUES(1115, '04-04-2021', 'Credit Card', 'Alex Feldon', 'Chicago', 500.00);
INSERT INTO Donation VALUES(1116, '05-05-2021', 'Cash', 'Christie Fraser', 'Phoenix', 600.00);
INSERT INTO Donation VALUES(1117, '06-06-2021', 'Direct Debit', 'Rob Davis', 'Phoenix', 700.00);
INSERT INTO Donation VALUES(1118, '07-07-2021', 'Direct Debit', 'Patricia Brown', 'Houston', 800.00);
INSERT INTO Donation VALUES(1119, '08-08-2021', 'Direct Debit', 'Justin Jones', 'Houston', 900.00);
INSERT INTO Donation VALUES(1120, '09-09-2021', 'Cash', 'Will Smith', 'San Jose', 100.00);
INSERT INTO Donation VALUES(1121, '10-10-2021', 'Cheque', 'Angela Jolie', 'San Jose', 200.00);
INSERT INTO Donation VALUES(1122, '11-11-2021', 'Cheque', 'Jenny Aniston', 'San Jose', 300.00);
INSERT INTO Donation VALUES(1123, '12-12-2021', 'Direct Debit', 'Grace Smith', 'New York', 400.00);
INSERT INTO Donation VALUES(1124, '01-01-2022', 'Cheque', 'Adam Williams', 'Chicago', 500.00);
INSERT INTO Donation VALUES(1125, '01-01-2022', 'Credit Card', 'Katie Smith', 'Los Angeles', 100.00);
INSERT INTO Donation VALUES(1126, '01-01-2022', 'Cheque', 'Donna Taylor', 'New York', 200.00);
INSERT INTO Donation VALUES(1127, '01-01-2022', 'Credit Card', 'Emma Smith', 'Phoenix', 300.00);
INSERT INTO Donation VALUES(1128, '01-01-2022', 'Cash', 'Mia Smith', 'Phoenix', 400.00);

Example 1

If you want to find the total donation per city, you can use the following SQL query:

SELECT DonorCity AS City, SUM(Amount) AS Amount
FROM Donation
GROUP BY DonorCity

The output will be as below:

If you want to transform the above result from being displayed as rows to being displayed as columns, you can use the SQL PIVOT operator. Below is the SQL query to do so:

SELECT 'Amount' AS 'City',   
  [Chicago], [Houston], [Los Angeles], [New York], [Phoenix], [San Jose]
FROM  
(
	SELECT DonorCity, Amount FROM Donation
 ) AS SourceTable
 PIVOT
 (
	SUM(Amount)
	FOR DonorCity IN ([Chicago], [Houston], [Los Angeles], [New York], [Phoenix], [San Jose])
 ) AS PivotTable

The output will be as below:

Example 2

If you want to find the total donation per city per year, you can use the following SQL query:

 SELECT DonorCity, DATEPART(YEAR, DonationDate) AS [Year], SUM(Amount) AS Amount
 FROM Donation
 GROUP BY DonorCity, DATEPART(YEAR, DonationDate)
 ORDER BY DonorCity, DATEPART(YEAR, DonationDate)

The output will be as below:

You can use PIVOT to display the result as columns. The query is below:

SELECT [Year],  
  [Chicago], [Houston], [Los Angeles], [New York], [Phoenix], [San Jose]
FROM  
(
	SELECT DonorCity, DATEPART(YEAR, DonationDate) AS [Year], Amount FROM Donation
 ) AS SourceTable
 PIVOT
 (
	SUM(Amount)
	FOR DonorCity IN ([Chicago], [Houston], [Los Angeles], [New York], [Phoenix], [San Jose])
 ) AS PivotTable

The output will be as below

References

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16