CREATE TABLE Student
(
    StudentName NVARCHAR(50),
    Subject NVARCHAR(50),
    Score INT
);

INSERT INTO Student(StudentName, Subject, Score)
VALUES ('Tom', 'Math', 80),
       ('Tom', 'English', 90),
       ('Tom', 'History', 85),
       ('John', 'Math', 75),
       ('John', 'English', 85),
       ('John', 'History', 80);

SELECT *
FROM 
( 
    SELECT StudentName, Subject, Score
    FROM Student
) AS SourceTable
PIVOT
(
    SUM(Score)
    FOR Subject IN ([Math], [English], [History])
) AS PivotTable;

StudentName | Math | English | History
-------------------------------------
Tom         | 80   | 90      | 85
John        | 75   | 85      | 80

CREATE TABLE Sales
(
    OrderDate DATE,
    Product NVARCHAR(50),
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales(OrderDate, Product, Amount)
VALUES ('2021-01-01', 'ProductA', 1000),
       ('2021-01-01', 'ProductB', 1500),
       ('2021-01-02', 'ProductA', 2000),
       ('2021-01-02', 'ProductB', 2500);

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(OrderDate)
FROM (SELECT DISTINCT OrderDate FROM Sales) AS OrderDates

SET @DynamicPivotQuery = 
N'SELECT Product, ' + @ColumnName + '
FROM
(
    SELECT OrderDate, Product, Amount
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR OrderDate IN (' + @ColumnName + ')
) AS PivotTable;'

EXEC(@DynamicPivotQuery);

Product   | 2021-01-01 | 2021-01-02
---------------------------------
ProductA  | 1000       | 2000
ProductB  | 1500       | 2500