Reporting modules often require dynamic pivoting —turning rows into columns based on varying data such as months, statuses, categories, locations, or user-defined fields.
Since these column values change over time, a static PIVOT query won’t work.
A Dynamic Pivot Stored Procedure gives full flexibility for:
Financial and accounting reports
Inventory and warehouse analytics
Attendance and HR dashboards
Sales reporting
Audit or workflow matrices
This article explains how to create a fully reusable Dynamic Pivot SP that works for any report model.
1. High-Level Architecture Diagram
+-------------+ Calls +---------------------------+| Frontend |------------------->| API / Report Service || (Angular) | +------------+--------------++-------------+ |
| Executes
v
+---------------------------+
| Dynamic Pivot Stored Proc |
| (SQL Server) |
+------------+--------------+
|
v
+---------------------------+
| Resulting Pivot Data |
+---------------------------+
2. What a Dynamic Pivot Solves
A dynamic pivot supports:
Dynamic column headers (e.g., Jan, Feb, Mar…)
Dynamic values (e.g., Totals, Counts, Sums)
Variable groupings (e.g., Region, Department, Category)
Runtime filters (date range, customer, status)
Example: Convert this:
| Employee | Month | Hours |
|---|
| Rajesh | Jan | 160 |
| Rajesh | Feb | 150 |
| Amit | Jan | 140 |
| Amit | Feb | 145 |
Into:
| Employee | Jan | Feb |
|---|
| Rajesh | 160 | 150 |
| Amit | 140 | 145 |
This requires dynamic SQL.
3. Core Structure of a Dynamic Pivot Procedure
A reusable procedure includes:
Generating dynamic column list
Building the PIVOT query
Executing using sp_executesql
Returning the final dataset
4. Full Dynamic Pivot Stored Procedure (Reusable)
CREATE OR ALTER PROCEDURE usp_DynamicPivotReport
(
@TableName NVARCHAR(200),
@RowField NVARCHAR(200), -- Stays fixed
@ColumnField NVARCHAR(200), -- Dynamic Column Names
@ValueField NVARCHAR(200), -- Aggregated Value Field
@WhereClause NVARCHAR(MAX) = '', -- Optional Filtering
@AggregateFunction NVARCHAR(20) = 'SUM' -- COUNT, SUM, AVG, MAX, MIN
)
ASBEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Columns NVARCHAR(MAX);
------------------------------------------------------------
-- Get dynamic column list
------------------------------------------------------------
SET @Columns = (
SELECT DISTINCT QUOTENAME(CAST([Value] AS NVARCHAR(200)))
FROM (
SELECT DISTINCT CAST((SELECT TOP 1 @ColumnField) AS NVARCHAR(200)) AS KeyCol,
CAST((SELECT TOP 1 @ColumnField) AS NVARCHAR(200)) AS Value
) AS T
);
-- Fix: Using dynamic SQL to fetch column values properly
DECLARE @ColSQL NVARCHAR(MAX) =
'SELECT DISTINCT QUOTENAME(' + @ColumnField + N') AS Col
FROM ' + @TableName +
CASE WHEN @WhereClause <> '' THEN ' WHERE ' + @WhereClause ELSE '' END;
CREATE TABLE #Cols (Col NVARCHAR(200));
INSERT INTO #Cols EXEC (@ColSQL);
SELECT @Columns = STRING_AGG(Col, ',') FROM #Cols;
------------------------------------------------------------
-- Build final pivot SQL
------------------------------------------------------------
SET @SQL = '
SELECT ' + @RowField + ', ' + @Columns + '
FROM
(
SELECT ' + @RowField + ',
' + @ColumnField + ' AS PivotColumn,
' + @ValueField + ' AS PivotValue
FROM ' + @TableName + '
' + CASE WHEN @WhereClause <> '' THEN 'WHERE ' + @WhereClause ELSE '' END + '
) AS SourceTable
PIVOT
(
' + @AggregateFunction + '(PivotValue)
FOR PivotColumn IN (' + @Columns + ')
) AS PivotTable
ORDER BY ' + @RowField + ';
';
PRINT @SQL; -- For debugging
EXEC sp_executesql @SQL;
END
5. Example Usage
5.1 Monthly Hours Report
EXEC usp_DynamicPivotReport
@TableName = 'EmployeeHours',
@RowField = 'EmployeeName',
@ColumnField = 'MonthName',
@ValueField = 'Hours',
@WhereClause = 'Year = 2025',
@AggregateFunction = 'SUM';
5.2 Sales Pivot by Category
EXEC usp_DynamicPivotReport
@TableName = 'Sales',
@RowField = 'Region',
@ColumnField = 'Category',
@ValueField = 'TotalAmount',
@WhereClause = 'SaleDate >= ''2025-01-01''',
@AggregateFunction = 'SUM';
5.3 Status-Wise Ticket Count
EXEC usp_DynamicPivotReport
@TableName = 'Tickets',
@RowField = 'AssignedTo',
@ColumnField = 'Status',
@ValueField = 'TicketId',
@AggregateFunction = 'COUNT';
6. Sequence Diagram: Dynamic Pivot Execution Flow
Frontend (Angular)
|
| calls API
v
Backend (.NET API)
|
| Executes Stored Proc
v
SQL Server (usp_DynamicPivotReport)
|
| Builds Column List
|
| Builds Dynamic SQL
|
| Executes Pivot
v
Returns Pivoted Result
7. Integrating With ASP.NET Core API
Controller Example
[HttpPost("pivot-report")]
public async Task<IActionResult> GetPivotReport([FromBody] PivotRequest req)
{
var data = await _repo.ExecutePivot(req);
return Ok(data);
}
Repository
public async Task<IEnumerable<dynamic>> ExecutePivot(PivotRequest req)
{
return await _db.QueryAsync<dynamic>(
"usp_DynamicPivotReport",
new {
req.TableName,
req.RowField,
req.ColumnField,
req.ValueField,
req.WhereClause,
req.AggregateFunction
},
commandType: CommandType.StoredProcedure
);
}
8. Best Practices for Dynamic Pivots
| Area | Best Practice |
|---|
| Security | Validate table + column names to prevent SQL injection |
| Performance | Use proper indexing on row + column fields |
| Avoid NULLs | Wrap value: ISNULL(ValueField, 0) |
| Large Datasets | Use temp tables before pivot |
| UI Integration | Keep pivot rows minimal; too many columns cause grid overflow |
9. When to Use Dynamic Pivoting
Recommended For
Reports where column names change dynamically
Month/Quarter-based analytics
Status or category-based aggregation
Data warehouse summary reports
Avoid When
10. Conclusion
A Dynamic Pivot Stored Procedure is one of the most powerful SQL templates for reporting systems. It gives your application:
Once built, this SP becomes the backbone for dozens of report use cases.
Join the conversation! Your thoughts help the community grow.