Using the *PIVOT* operator in SQL to transform rows into columns dynamically can be quite complex, as standard SQL doesn't directly support dynamic pivoting. However, you can achieve dynamic pivoting by constructing a dynamic SQL statement.
Here's how to do it step by step:
###### 1. Understand the Data

Assume that you have a sample table named *Sales* with the following columns:
- *Product*
- *Year*
- *SalesAmount*
Your data might look like this:
| Product | Year | SalesAmount |
|---------|------|-------------|
| A | 2021 | 100 |
| A | 2022 | 150 |
| B | 2021 | 200 |
| B | 2022 | 250 |

###### 2. Define the Dynamic SQL Structure

You would first need to construct a SQL query that determines the distinct values in the column you want to pivot (e.g., the *Year* column) and then use those values to build the pivot query.
###### 3. Dynamic SQL for PIVOT

You can use the following approach in SQL Server:
DECLARE @columns AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);
-- Get distinct years for the columns of the pivot
SELECT @columns = STRING_AGG(QUOTENAME(Year), ', ')
FROM (SELECT DISTINCT Year FROM Sales) AS YearList;
-- Construct the dynamic SQL for the pivot operation
SET @sql = '
SELECT Product, ' + @columns + '
FROM
(
SELECT Product, Year, SalesAmount
FROM Sales
) AS SourceTable
PIVOT
(
SUM(SalesAmount)
FOR Year IN (' + @columns + ')
) AS PivotTable;';
-- Execute the dynamic SQL
EXEC sp_executesql @sql;

###### Explanation of the Code:

1. **Capture the Columns**: The first part captures distinct *Year* values and aggregates them into a comma-separated list suitable for the *IN* clause of the *PIVOT* statement.
2. **Construct the Dynamic SQL**: The second part forms the dynamic SQL string, substituting in the *@columns* variable to create the *PIVOT* structure.
3. **Execute the SQL**: Finally, you run the dynamic SQL using *sp_executesql*.
###### Important Notes:

- Make sure your SQL Server instance has the *STRING_AGG* function available (it was introduced in SQL Server 2017); if you are using an earlier version, you might need to use *FOR XML PATH* or another method to aggregate your column names.
- Handle SQL Injection if user input is involved. Dynamic SQL can be vulnerable if not handled properly.
This method enables you to pivot your data dynamically based on the distinct values found in the pivot column.