SQL Exercise -10

SQL Exercise:

You are provided with a table Sales that records monthly sales data for various products. Write a query to transform the data such that each product has its sales figures displayed across columns representing months.

Create table script:

CREATE TABLE Sales (
Product VARCHAR(50),
Month VARCHAR(20),
SalesAmount DECIMAL(10, 2)
);

Insert sample data:

INSERT INTO Sales (Product, Month, SalesAmount)
VALUES
('ProductA', 'January', 5000.00),
('ProductA', 'February', 7000.00),
('ProductA', 'March', 8000.00),
('ProductB', 'January', 4000.00),
('ProductB', 'February', 3000.00),
('ProductB', 'March', 6000.00),
('ProductC', 'January', 9000.00),
('ProductC', 'February', 5000.00),
('ProductC', 'March', 7000.00);

Solution:

SELECT
Product,
ISNULL([January], 0) AS January,
ISNULL([February], 0) AS February,
ISNULL([March], 0) AS March
FROM
(SELECT Product, Month, SalesAmount FROM Sales) AS SourceTable
PIVOT
(
SUM(SalesAmount)
FOR Month IN ([January], [February], [March])
) AS PivotTable;

Output:

 

Explanation:

• Source Table: The inner query selects Product, Month, and SalesAmount as the base data for pivoting.
• PIVOT Clause:
  • SUM(SalesAmount): Aggregates the sales amount for each product across the months.
  • FOR Month IN ([January], [February], [March]): Transforms rows for each month into columns.
• ISNULL: Replaces any NULL values with 0 for months without sales data.

 

SQL Exercise – Dynamic pivoting