PIVOT

In SQL Server, a pivot table transforms rows into columns, allowing you to summarize and aggregate data for better readability and analysis. This is commonly achieved using the PIVOT operator.

---

Steps to Create a Pivot Table

1. Identify the Base Data: Your base table should contain:
   • Rows you want to pivot.
   • Column values to become the new column headers.
   • Values to fill in those columns.
2. Write the Query: Use the PIVOT operator to perform the transformation.

---

Syntax for PIVOT:

SELECT <column_list>
FROM (
      <source_query>
     ) AS source
PIVOT (
       <aggregation_function>(<value_column>)
       FOR <pivot_column> IN (<pivot_column_values>)
     ) AS pivot_table;

• <source_query>: The query that retrieves the base data.
• <value_column>: The column whose values will be aggregated.
• <pivot_column>: The column whose unique values will become new column headers.
• <pivot_column_values>: List of column values to pivot.

---

Example: Hacker rank

Example 1: Sales Data by Year

Base Table (SalesData):

Product	Year	Sales
A	2020	100
A	2021	150
B	2020	200
B	2021	250

Pivot Query:

SELECT Product, [2020], [2021]
FROM (
      SELECT Product, Year, Sales
      FROM SalesData
     ) AS source
PIVOT (
       SUM(Sales)
       FOR Year IN ([2020], [2021])
     ) AS pivot_table;

Result:

Product	2020	2021
A	100	150
B	200	250

---

Example 2: Count of Orders by Status

Base Table (Orders):

OrderID	Status
1	Delivered
2	Pending
3	Delivered
4	Cancelled
5	Pending

Pivot Query:

SELECT [Delivered], [Pending], [Cancelled]
FROM (
      SELECT Status, COUNT(OrderID) AS OrderCount
      FROM Orders
      GROUP BY Status
     ) AS source
PIVOT (
       SUM(OrderCount)
       FOR Status IN ([Delivered], [Pending], [Cancelled])
     ) AS pivot_table;

Result:

Delivered	Pending	Cancelled
2	2	1

---

Dynamic Pivot Table

If you don’t know the values in the pivot column beforehand, you can create a dynamic pivot table using dynamic SQL.

Dynamic Pivot Example:

DECLARE @columns NVARCHAR(MAX), @query NVARCHAR(MAX);
 
-- Step 1: Generate the list of pivot columns dynamically
SELECT @columns = STRING_AGG(QUOTENAME(Status), ',')
FROM (SELECT DISTINCT Status FROM Orders) AS column_list;
 
-- Step 2: Create the dynamic query
SET @query = '
SELECT ' + @columns + '
FROM (
      SELECT Status, COUNT(OrderID) AS OrderCount
      FROM Orders
      GROUP BY Status
     ) AS source
PIVOT (
       SUM(OrderCount)
       FOR Status IN (' + @columns + ')
     ) AS pivot_table;
';
 
-- Step 3: Execute the query
EXEC sp_executesql @query;

---

Notes:

1. Static Pivot:
   • Best for fixed, known column values in the pivot column.
2. Dynamic Pivot:
   • Use when the pivot column values are unknown or change frequently.
3. Aggregate Functions:
   • Commonly used functions include SUM, COUNT, AVG, etc.
4. Performance:
   • Ensure your base query is optimized for better performance.

---

By using PIVOT effectively, you can transform your data into a more meaningful format for reporting and analysis.