How to Pivot Rows to Columns in SQL Server (5 Methods)

Pivoting takes data stored vertically in rows and spreads it horizontally into columns. This is something you’ll likely encounter regularly when building reports or reshaping data for analysis. Basically, you’ve got data stored in rows, and you need to flip it so those row values become column headers. Maybe you’re building a report, maybe you’re feeding data to another system, or maybe the client just wants to see things the other way around.

SQL Server gives you several ways to handle this. Let’s walk through five different approaches, from the dedicated PIVOT operator to more flexible techniques that work when you need extra control.

Setting Up Sample Data

For our examples, we’ll use a simple dataset tracking software deployment status across different environments:

CREATE TABLE DeploymentStatus (
    ApplicationName VARCHAR(50),
    Environment VARCHAR(20),
    StatusCode VARCHAR(20)
);

INSERT INTO DeploymentStatus (ApplicationName, Environment, StatusCode)
VALUES 
    ('OrderAPI', 'Development', 'Deployed'),
    ('OrderAPI', 'Staging', 'Deployed'),
    ('OrderAPI', 'Production', 'Pending'),
    ('CustomerPortal', 'Development', 'Deployed'),
    ('CustomerPortal', 'Staging', 'Failed'),
    ('CustomerPortal', 'Production', 'NotStarted'),
    ('InventoryService', 'Development', 'Deployed'),
    ('InventoryService', 'Staging', 'Deployed'),
    ('InventoryService', 'Production', 'Deployed'),
    ('PaymentGateway', 'Development', 'Deployed'),
    ('PaymentGateway', 'Staging', 'Pending'),
    ('PaymentGateway', 'Production', 'NotStarted');

SELECT * FROM DeploymentStatus;

Output:

ApplicationName   Environment  StatusCode
----------------  -----------  ----------
OrderAPI          Development  Deployed  
OrderAPI          Staging      Deployed  
OrderAPI          Production   Pending   
CustomerPortal    Development  Deployed  
CustomerPortal    Staging      Failed    
CustomerPortal    Production   NotStarted
InventoryService  Development  Deployed  
InventoryService  Staging      Deployed  
InventoryService  Production   Deployed  
PaymentGateway    Development  Deployed  
PaymentGateway    Staging      Pending   
PaymentGateway    Production   NotStarted

Right now, we can see that this data is in a typical normalized format with one row per application-environment combination. We want to pivot it so each application gets one row with separate columns for each environment.

Method 1: The `PIVOT` Operator

The PIVOT operator is SQL Server’s built-in solution for this exact problem. It’s clean, readable, and performs well for straightforward cases.

Here’s an example of pivoting the above data:

SELECT ApplicationName, Development, Staging, Production
FROM (
    SELECT ApplicationName, Environment, StatusCode
    FROM DeploymentStatus
) AS SourceData
PIVOT (
    MAX(StatusCode)
    FOR Environment IN ([Development], [Staging], [Production])
) AS PivotTable
ORDER BY ApplicationName;

Result:

ApplicationName   Development  Staging   Production
----------------  -----------  --------  ----------
CustomerPortal    Deployed     Failed    NotStarted
InventoryService  Deployed     Deployed  Deployed  
OrderAPI          Deployed     Deployed  Pending   
PaymentGateway    Deployed     Pending   NotStarted

The main parts here are the subquery that selects your source data, the aggregate function (MAX() in this case, though it doesn’t matter much when you only have one value per combination), and the IN clause that specifies which column values should become headers.

One thing to remember is that PIVOT requires an aggregate function even when you’re not really aggregating anything. That’s just how it works. MAX() or MIN() both do the job fine when you know there’s only one value per cell.

Method 2: `CASE` Statements

Before PIVOT existed, everyone used CASE statements along with the GROUP BY clause, and honestly, this approach still has its advantages. It’s more verbose but also more transparent about what’s happening:

SELECT 
    ApplicationName,
    MAX(CASE WHEN Environment = 'Development' THEN StatusCode END) AS Development,
    MAX(CASE WHEN Environment = 'Staging' THEN StatusCode END) AS Staging,
    MAX(CASE WHEN Environment = 'Production' THEN StatusCode END) AS Production
FROM DeploymentStatus
GROUP BY ApplicationName
ORDER BY ApplicationName;

Result:

ApplicationName   Development  Staging   Production
----------------  -----------  --------  ----------
CustomerPortal    Deployed     Failed    NotStarted
InventoryService  Deployed     Deployed  Deployed  
OrderAPI          Deployed     Deployed  Pending   
PaymentGateway    Deployed     Pending   NotStarted

This method groups by the identifier column and uses conditional aggregation to pull out values for each environment. The MAX() function collapses the NULLs from the CASE statement, leaving you with just the actual values.

The nice thing about CASE statements is you get complete control over the logic. You can apply different conditions for different columns for instance, or you can transform values on the fly. PIVOT is more rigid in comparison.

Method 3: Dynamic SQL with `PIVOT`

What if you don’t know ahead of time which environments exist in your data? Maybe new environments get added, or you’re writing a query that needs to work across different datasets. That’s where dynamic SQL can help.

Here’s an example that uses the PIVOT operator with dynamic SQL:

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

SELECT @columns = STRING_AGG(QUOTENAME(Environment), ', ')
FROM (SELECT DISTINCT Environment FROM DeploymentStatus) AS Environments;

SET @sql = N'
SELECT ApplicationName, ' + @columns + '
FROM (
    SELECT ApplicationName, Environment, StatusCode
    FROM DeploymentStatus
) AS SourceData
PIVOT (
    MAX(StatusCode)
    FOR Environment IN (' + @columns + ')
) AS PivotTable
ORDER BY ApplicationName;';

EXEC sp_executesql @sql;

Result:

ApplicationName   Development  Production  Staging 
----------------  -----------  ----------  --------
CustomerPortal    Deployed     NotStarted  Failed  
InventoryService  Deployed     Deployed    Deployed
OrderAPI          Deployed     Pending     Deployed
PaymentGateway    Deployed     NotStarted  Pending

This approach queries the distinct values from your Environment column, builds the column list dynamically, and constructs the entire PIVOT query as a string before executing it. It’s more complex, but it adapts automatically to your data.

The STRING_AGG() function (available in SQL Server 2017+) makes this cleaner than older methods that used FOR XML PATH. If you’re stuck on an older version, you’ll need to use that XML trick instead, but the concept is the same.

Method 4: Dynamic SQL with `CASE` Statements

You can also build dynamic CASE statements, which combines the flexibility of CASE with the adaptability of dynamic SQL:

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

SELECT @columns = STRING_AGG(
    'MAX(CASE WHEN Environment = ' + QUOTENAME(Environment, '''') + 
    ' THEN StatusCode END) AS ' + QUOTENAME(Environment), 
    ', '
)
FROM (SELECT DISTINCT Environment FROM DeploymentStatus) AS Environments;

SET @sql = N'
SELECT ApplicationName, ' + @columns + '
FROM DeploymentStatus
GROUP BY ApplicationName
ORDER BY ApplicationName;';

EXEC sp_executesql @sql;

Result:

ApplicationName   Development  Production  Staging 
----------------  -----------  ----------  --------
CustomerPortal    Deployed     NotStarted  Failed  
InventoryService  Deployed     Deployed    Deployed
OrderAPI          Deployed     Pending     Deployed
PaymentGateway    Deployed     NotStarted  Pending

This generates one CASE statement per environment value found in the data. It’s arguably more straightforward than dynamic PIVOT since you’re just building a SELECT list rather than wrapping everything in a subquery and PIVOT clause.

Method 5: Cursor-Based Approach

I’ll be honest. I’m including this method for completeness, not because I recommend it. Cursors get a bad rap in SQL, and usually for good reason. But sometimes you need to pivot data in a context where dynamic SQL isn’t allowed or where you’re building complex business logic around each value. In such situations you may have a valid case for using the cursor approach.

Here’s an example of doing the job with a cursor:

DECLARE @ApplicationName VARCHAR(50);
DECLARE @Development VARCHAR(20);
DECLARE @Staging VARCHAR(20);
DECLARE @Production VARCHAR(20);

DECLARE @Results TABLE (
    ApplicationName VARCHAR(50),
    Development VARCHAR(20),
    Staging VARCHAR(20),
    Production VARCHAR(20)
);

DECLARE app_cursor CURSOR FOR
    SELECT DISTINCT ApplicationName FROM DeploymentStatus;

OPEN app_cursor;
FETCH NEXT FROM app_cursor INTO @ApplicationName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @Development = StatusCode FROM DeploymentStatus 
    WHERE ApplicationName = @ApplicationName AND Environment = 'Development';
    
    SELECT @Staging = StatusCode FROM DeploymentStatus 
    WHERE ApplicationName = @ApplicationName AND Environment = 'Staging';
    
    SELECT @Production = StatusCode FROM DeploymentStatus 
    WHERE ApplicationName = @ApplicationName AND Environment = 'Production';
    
    INSERT INTO @Results (ApplicationName, Development, Staging, Production)
    VALUES (@ApplicationName, @Development, @Staging, @Production);
    
    SET @Development = NULL;
    SET @Staging = NULL;
    SET @Production = NULL;
    
    FETCH NEXT FROM app_cursor INTO @ApplicationName;
END;

CLOSE app_cursor;
DEALLOCATE app_cursor;

SELECT * FROM @Results ORDER BY ApplicationName;

Result:

ApplicationName   Development  Staging   Production
----------------  -----------  --------  ----------
CustomerPortal    Deployed     Failed    NotStarted
InventoryService  Deployed     Deployed  Deployed  
OrderAPI          Deployed     Deployed  Pending   
PaymentGateway    Deployed     Pending   NotStarted

This iterates through each application, fetches the status for each environment, and builds the result set row by row. Performance-wise, it’s the slowest option here. It’s also the most verbose. But on the flip side, it does give you maximum control in case you need to add complex logic or error handling for each cell.

Which Method Should You Use?

For most scenarios, you’ll probably want to start with the standard PIVOT operator. It’s built for this purpose, it’s easy to read, and SQL Server’s query optimizer understands it well.

If you need more control over the transformation logic or want to apply different conditions to different columns, switch to CASE statements.

Dynamic SQL becomes necessary when your column values aren’t known at development time. Just be aware of the security implications. In particular, make sure you’re not concatenating user input directly into your dynamic SQL strings, or you’ll be opening yourself up to SQL injection attacks.

As for cursors, save them for the rare cases where you’re doing something unusual that doesn’t fit the set-based approaches. They’re procedural by nature, which goes against SQL’s strengths, but sometimes that’s exactly what you need.

The deployment status example we used here is simple, but these same techniques scale up to more complex scenarios. Pick the method that fits your situation, and you’ll have rows pivoting to columns in no time.

Setting Up Sample Data

Method 1: The PIVOT Operator

Method 2: CASE Statements

Method 3: Dynamic SQL with PIVOT

Method 4: Dynamic SQL with CASE Statements

Method 5: Cursor-Based Approach

Which Method Should You Use?

Method 1: The `PIVOT` Operator

Method 2: `CASE` Statements

Method 3: Dynamic SQL with `PIVOT`

Method 4: Dynamic SQL with `CASE` Statements