The PIVOT function is a powerful tool in SQL Server that allows you to rotate rows into columns. Here’s how to use it:

1. Basic Syntax:```sqlSELECT , [Column1], [Column2], ...FROM(SELECT , FROM <table_name>) AS SourceTablePIVOT(()FOR IN ([Column1], [Column2], ...)) AS PivotTable;```
2. Example:Suppose you have a table named `Sales` with the following structure:```plaintext| Year | Product | Amount ||------|---------|--------|| 2020 | A | 100 || 2020 | B | 150 || 2021 | A | 200 || 2021 | B | 250 |```To convert the `Product` rows into columns, you can use the following query:```sqlSELECT Year, [A] AS ProductA, [B] AS ProductBFROM(SELECT Year, Product, AmountFROM Sales) AS SourceTablePIVOT(SUM(Amount)FOR Product IN ([A], [B])) AS PivotTable;```This will result in:```plaintext| Year | ProductA | ProductB ||------|----------|----------|| 2020 | 100 | 150 || 2021 | 200 | 250 |```

If you have an unknown number of columns or prefer not to use PIVOT, you can also achieve similar results using CASE statements. Here’s an example:```sqlSELECT Year,SUM(CASE WHEN Product = 'A' THEN Amount ELSE 0 END) AS ProductA,SUM(CASE WHEN Product = 'B' THEN Amount ELSE 0 END) AS ProductBFROM SalesGROUP BY Year;```This method provides flexibility, especially when dealing with dynamic column names or when the number of unique values in the pivot column is not known in advance.

Using the PIVOT function or CASE statements, you can effectively convert rows to columns in SQL Server, enhancing the readability and usability of your data for reporting and analysis.