Static vs Dynamic Pivot in SQL Server: A Complete Guide with Real Example

In real-world applications like ERP, costing, or inventory systems, it's common to represent data in a pivot format—with rows turned into columns for better analysis. But here's the challenge: what happens when the column values (types) change over time?

This article explains both static and dynamic pivoting in SQL Server, and shows when to use which—complete with examples, pros, and cons.

---

📌 Problem Statement

You have a costing system where cost items are associated with different types like:

CopyEditTRIMS, CM, SHELL, LINING, ACCESSORIES...

These types come from two sources:

• tblCostingTrimItems.[type]

• tblOsCostingFabric.FabType

You want a pivot table showing each fileNos with the total cost of each type as columns.

---

✅ Static Pivot: Hardcoded Column Names

This is the easiest method, suitable only when your column values are fixed.

🧾 Static Pivot Query

sqlCopyEditSELECT fileNos, buyer, FOB, Season, ordHndlBank, [TRIMS], [CM], [SHELL], [LINING]
FROM (
    -- Get all cost data from both tables
    SELECT om.fileNos, OM.buyer, OM.FOB, ocm.Season, OME.ordHndlBank,
           tit.[type] AS Type,
           ISNULL(stl.Qty * other.OtherCost / 12, 0) AS Cost
    FROM tblOrderMaster OM 
    JOIN tblOsCostingMaster ocm ON OM.fileEntryId = ocm.fileNos
    JOIN tblOsCostingLink oscl ON ocm.EntryId = oscl.masterEntryId
    JOIN tblOsCostingStyleLink stl ON oscl.OSCostId = stl.OSCostId
    JOIN tblOsCostingOther other ON stl.OSCostId = other.osCostId
    LEFT JOIN tblCostingTrimItems tit ON other.OtherType = tit.Items
    LEFT JOIN tblOrderMasterExtended OME ON OME.fileEntryId = OM.fileEntryId
    WHERE om.fileNos = 'EG5-08590'

    UNION ALL

    SELECT om.fileNos, OM.buyer, OM.FOB, ocm.Season, OME.ordHndlBank,
           Fabric.FabType AS Type,
           ISNULL(stl.Qty * other.OtherCost / 12, 0) AS Cost
    FROM tblOrderMaster OM 
    JOIN tblOsCostingMaster ocm ON OM.fileEntryId = ocm.fileNos
    JOIN tblOsCostingLink oscl ON ocm.EntryId = oscl.masterEntryId
    JOIN tblOsCostingStyleLink stl ON oscl.OSCostId = stl.OSCostId
    JOIN tblOsCostingOther other ON stl.OSCostId = other.osCostId
    JOIN tblOsCostingFabric Fabric ON stl.OSCostId = Fabric.osCostId
    LEFT JOIN tblOrderMasterExtended OME ON OME.fileEntryId = OM.fileEntryId
    WHERE om.fileNos = 'EG5-08590'
) AS RawData
PIVOT (
    SUM(Cost) FOR Type IN ([TRIMS], [CM], [SHELL], [LINING])
) AS PivotTable;

---

✅ Pros of Static Pivot

• ✅ Simple to write

• ✅ Easy to read and debug

• ✅ Fast for small and fixed scenarios

❌ Cons of Static Pivot

• ❌ Fails if new types are introduced (e.g., ACCESSORIES)

• ❌ Requires manual update

• ❌ Not scalable for dynamic datasets

---

🔄 Dynamic Pivot: Auto-Generate Columns

This method solves the static pivot's limitations by dynamically building the column list and pivoting automatically.

🧾 Dynamic Pivot Query (Using sp_executesql)

DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 1. Get all unique types
SELECT @columns = STRING_AGG(QUOTENAME(Type), ',')
FROM (
    SELECT DISTINCT [type] AS Type FROM tblCostingTrimItems WHERE [type] IS NOT NULL
    UNION
    SELECT DISTINCT FabType AS Type FROM tblOsCostingFabric WHERE FabType IS NOT NULL
) AS TypeList;

-- 2. Build full query
SET @sql = '
WITH CostData AS (
    SELECT om.fileNos, OM.buyer, OM.FOB, ocm.Season, OME.ordHndlBank,
           tit.[type] AS Type,
           ISNULL(stl.Qty * other.OtherCost / 12, 0) AS Cost
    FROM tblOrderMaster OM 
    LEFT JOIN tblOsCostingMaster ocm ON OM.fileEntryId = ocm.fileNos
    LEFT JOIN tblOsCostingLink oscl ON ocm.EntryId = oscl.masterEntryId
    LEFT JOIN tblOsCostingStyleLink stl ON oscl.OSCostId = stl.OSCostId
    LEFT JOIN tblOsCostingOther other ON stl.OSCostId = other.osCostId
    LEFT JOIN tblCostingTrimItems tit ON other.OtherType = tit.Items
    LEFT JOIN tblOrderMasterExtended OME ON OME.fileEntryId = OM.fileEntryId
    WHERE om.fileNos = ''EG5-08590''

    UNION ALL

    SELECT om.fileNos, OM.buyer, OM.FOB, ocm.Season, OME.ordHndlBank,
           Fabric.FabType AS Type,
           ISNULL(stl.Qty * other.OtherCost / 12, 0) AS Cost
    FROM tblOrderMaster OM 
    LEFT JOIN tblOsCostingMaster ocm ON OM.fileEntryId = ocm.fileNos
    LEFT JOIN tblOsCostingLink oscl ON ocm.EntryId = oscl.masterEntryId
    LEFT JOIN tblOsCostingStyleLink stl ON oscl.OSCostId = stl.OSCostId
    LEFT JOIN tblOsCostingOther other ON stl.OSCostId = other.osCostId
    JOIN tblOsCostingFabric Fabric ON stl.OSCostId = Fabric.osCostId
    LEFT JOIN tblOrderMasterExtended OME ON OME.fileEntryId = OM.fileEntryId
    WHERE om.fileNos = ''EG5-08590''
)

SELECT fileNos, buyer, FOB, Season, ordHndlBank, ' + @columns + '
FROM CostData
PIVOT (
    SUM(Cost)
    FOR Type IN (' + @columns + ')
) AS PivotTable;
';

-- 3. Execute the dynamic SQL
EXEC sp_executesql @sql;

---

✅ Pros of Dynamic Pivot

• ✅ Automatically includes new types (e.g., ACCESSORIES, LABELS)

• ✅ Future-proof and scalable

• ✅ Ideal for reporting or BI dashboards

❌ Cons of Dynamic Pivot

• ❌ Harder to read/debug

• ❌ Cannot use in direct JOINs or subqueries easily

• ❌ May require additional permissions to run dynamic SQL

---

🧠 Summary Comparison Table

---

💡 Bonus Tip: Use a Stored Procedure

You can easily wrap the dynamic pivot in a stored procedure:

CREATE PROCEDURE sp_GetCostingPivot @FileNo NVARCHAR(50)
AS
BEGIN
    -- Use @FileNo in place of 'EG5-08590' in dynamic query
END

Then call like:

EXEC sp_GetCostingPivot 'EG5-08590';

---

✅ Conclusion

Both static and dynamic pivot queries have their place in SQL development.

• Use static pivot for simple, well-defined reports.

• Use dynamic pivot when dealing with data that evolves, especially for reporting systems.

Understanding when and how to use each technique improves your SQL flexibility, code maintainability, and report scalability.