SQL Server  

🧩 Convert SQL Server DataTable Rows into Pivot Table (With Example)

📘 Introduction

In real-world database applications, data is often stored in a normalized (row-based) format — but sometimes, it’s easier to analyze data when it's displayed in a pivoted (column-based) format.

For example, you might have monthly sales data stored as rows, but you want to display it as a table with months as columns and sales amounts as values.

SQL Server provides an efficient way to achieve this using the PIVOT operator.

⚙️ Understanding the Pivot Operator

The PIVOT operator in SQL Server rotates rows into columns.
It’s most commonly used for summarizing and aggregating data in reporting or analytics queries.

Syntax

SELECT <non-pivoted column>,
       [first pivoted column],
       [second pivoted column],
       ...
FROM
(
    <source_table_query>
) AS SourceTable
PIVOT
(
    <aggregation_function>(<column_being_aggregated>)
    FOR <column_to_pivot> IN ([first pivoted column], [second pivoted column], ...)
) AS PivotTable;

🧮 Example: Convert Rows to Columns using Pivot

🎯 Scenario

Suppose we have a table named SalesData:

YearMonthSalesAmount
2024Jan12000
2024Feb15000
2024Mar10000
2025Jan17000
2025Feb19000
2025Mar20000

✅ Goal

We want to pivot this data to show months as columns and sales totals per year.

🧠 Step 1. Create the Table and Insert Data

CREATE TABLE SalesData (
    [Year] INT,
    [Month] VARCHAR(10),
    [SalesAmount] INT
);

INSERT INTO SalesData ([Year], [Month], [SalesAmount])
VALUES
(2024, 'Jan', 12000),
(2024, 'Feb', 15000),
(2024, 'Mar', 10000),
(2025, 'Jan', 17000),
(2025, 'Feb', 19000),
(2025, 'Mar', 20000);

🧠 Step 2. Apply the PIVOT Query

SELECT 
    [Year],
    ISNULL([Jan], 0) AS Jan,
    ISNULL([Feb], 0) AS Feb,
    ISNULL([Mar], 0) AS Mar
FROM 
(
    SELECT [Year], [Month], [SalesAmount]
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR [Month] IN ([Jan], [Feb], [Mar])
) AS PivotTable;

📊 Output

YearJanFebMar
2024120001500010000
2025170001900020000

🎉 Now your data is transformed from row-based to column-based format!

🔁 Dynamic Pivot Example (Auto Columns)

If you don’t know which months (or pivot values) exist in advance, use dynamic SQL to generate columns automatically.

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME([Month]), ',')
FROM (SELECT DISTINCT [Month] FROM SalesData) AS M;

SET @query = '
SELECT [Year], ' + @cols + '
FROM 
(
    SELECT [Year], [Month], [SalesAmount]
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR [Month] IN (' + @cols + ')
) AS PivotTable
ORDER BY [Year];
';

EXEC sp_executesql @query;

🪄 Result:
The output dynamically adjusts based on the available Month values in your table — ideal for reporting dashboards.

🧰 When to Use Pivot Tables

✅ Use Pivot when:

  • You need a summary view of data (e.g., sales, attendance, performance).

  • You want fixed or known column names for quick reporting.

  • You’re working on BI dashboards, SSRS, or Excel exports.

⚠️ Avoid Pivot when:

  • Data needs frequent schema changes.

  • You have too many unique column values, leading to wide, inefficient tables.

📘 Related SQL Techniques

  • UNPIVOT: Convert columns back into rows.

  • GROUP BY with CASE WHEN: Manual alternative for pivoting small datasets.

Example

SELECT 
    [Year],
    SUM(CASE WHEN [Month] = 'Jan' THEN SalesAmount ELSE 0 END) AS Jan,
    SUM(CASE WHEN [Month] = 'Feb' THEN SalesAmount ELSE 0 END) AS Feb,
    SUM(CASE WHEN [Month] = 'Mar' THEN SalesAmount ELSE 0 END) AS Mar
FROM SalesData
GROUP BY [Year];

🏁 Conclusion

Pivoting data in SQL Server is a powerful technique for transforming row-based data into a report-ready format. Whether you’re working with static columns or dynamic data, SQL Server’s PIVOT and dynamic SQL capabilities make data visualization much easier.

Membership not found