SQL Server Convert Rows to Columns (Pivot)

Converting rows to columns in SQL Server is a common task, and it can be achieved using techniques like PIVOT, CASE statements, or dynamic SQL, depending on your specific requirements. Here, I'll provide examples of each method:

Suppose you have a table called "Sales" with the following columns: "ProductID," "Month," and "Revenue." You want to pivot the data to show each month's revenue as a separate column.

1. Using PIVOT:

```sql

SELECT *

FROM (

    SELECT ProductID, Month, Revenue

    FROM Sales

) AS SourceTable

PIVOT (

    SUM(Revenue)

    FOR Month IN ([January], [February], [March], [April], [May], [June])

) AS PivotTable;

```

In this example, you explicitly specify the months for which you want to pivot the data.

2. Using CASE Statements:

```sql

SELECT ProductID,

       SUM(CASE WHEN Month = 'January' THEN Revenue ELSE 0 END) AS January,

       SUM(CASE WHEN Month = 'February' THEN Revenue ELSE 0 END) AS February,

       SUM(CASE WHEN Month = 'March' THEN Revenue ELSE 0 END) AS March,

       SUM(CASE WHEN Month = 'April' THEN Revenue ELSE 0 END) AS April,

       SUM(CASE WHEN Month = 'May' THEN Revenue ELSE 0 END) AS May,

       SUM(CASE WHEN Month = 'June' THEN Revenue ELSE 0 END) AS June

FROM Sales

GROUP BY ProductID;

```

This approach uses a CASE statement to sum the revenue for each month and then groups the result by the "ProductID."

3. Using Dynamic SQL (for a dynamic number of columns):

If you have a dynamic number of months and want to pivot the data accordingly, you can use dynamic SQL. This example assumes you have a table called "Months" with a list of months to pivot for.

```sql

DECLARE @cols AS NVARCHAR(MAX);

DECLARE @query AS NVARCHAR(MAX);

SET @cols = STUFF((

    SELECT DISTINCT ',' + QUOTENAME(Month)

    FROM Months

    FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = '

SELECT ProductID, ' + @cols + '

FROM (

    SELECT ProductID, Month, Revenue

    FROM Sales

) AS SourceTable

PIVOT (

    SUM(Revenue)

    FOR Month IN (' + @cols + ')

) AS PivotTable;';

EXEC(@query);

```

This dynamic SQL approach allows you to pivot the data based on the months in the "Months" table.

Choose the method that best fits your specific scenario, whether you have a fixed set of columns to pivot or a dynamic requirement.