How to get sql pivot multiple columns?

To use SQL PIVOT for multiple columns, follow these steps depending on your database system. PIVOT is commonly used in SQL Server, while conditional aggregation is used in other databases like MySQL and PostgreSQL.

1. Using PIVOT in SQL Server

If you want to pivot multiple columns, use an aggregate function with PIVOT.

Example: Sales Data Pivoting (Year & Sales)

SELECT * 
FROM (
    SELECT SalesPerson, Year, Product, SalesAmount 
    FROM SalesData
) AS SourceTable  
PIVOT (
    SUM(SalesAmount) 
    FOR Year IN ([2022], [2023], [2024])
) AS PivotTable;

To pivot multiple columns in SQL, you can use the PIVOT function along with aggregation functions like MAX, MIN, SUM, etc., to transform row values into columns. Here's a general syntax for pivoting multiple columns:

SELECT pivot_column,
       [pivot_value1], [pivot_value2], ..., [pivot_valueN]
FROM (
    SELECT group_column, pivot_column, value_column1, value_column2, ..., value_columnN
    FROM your_table
) AS SourceTable
PIVOT (
    aggregation_function(value_column)
    FOR pivot_column IN ([pivot_value1], [pivot_value2], ..., [pivot_valueN])
) AS PivotTable;

Let's break down the syntax:

• pivot_column: This is the column whose distinct values will become the new columns.
  
• [pivot_value1], [pivot_value2], ..., [pivot_valueN]: These are the values from the pivot_column that you want to pivot into columns.
  
• group_column: This column will remain in the result set to group the data.
  
• value_column1, value_column2, ..., value_columnN: These are the columns whose values you want to pivot.
  
• aggregation_function: It's applied to aggregate the values in value_column for each combination of group_column and pivot_column.

Here's a simple example to illustrate:

Let's say you have a table Sales with columns Year, Month, Product, and Revenue. You want to pivot the Product and Revenue columns based on the Year and Month.

  SELECT Year, Month,       [Product1_Revenue] AS Product1,       [Product2_Revenue] AS Product2,       [Product3_Revenue] AS Product3FROM (    SELECT Year, Month, Product, Revenue    FROM Sales) AS SourceTablePIVOT (    SUM(Revenue)    FOR Product IN ([Product1_Revenue], [Product2_Revenue], [Product3_Revenue])) AS PivotTable;

This query will pivot the Product and Revenue columns into separate columns for each Year and Month, summing up the revenues for each product.

Adjust the column names and aggregation functions (SUM, MAX, MIN, etc.) according to your specific requirements.