Not every DBMS includes a dedicated PIVOT operator. And even in DBMSs like SQL Server that do provide a PIVOT operator, you might prefer alternatives for better readability or more control over the transformation logic. Fortunately, you can pivot data using standard SQL techniques that work across virtually any relational database.
The main approach is conditional aggregation. This is where you use CASE statements within aggregate functions to selectively pull values into specific columns. It tends to be more verbose than using a PIVOT operator, but it’s also more transparent, more flexible, and completely portable across database platforms.
Why You Might Skip PIVOT
Well first of all, you might not have a choice. If you’re using a DBMS that doesn’t have a dedicated PIVOT operator, then you’ll need to find another way to create a pivot table. The techniques presented here could be your only option for pivoting within the database layer.
But even if your DBMS does provide a PIVOT operator (such as SQL Server), you may sometimes find yourself looking for alternative methods. CASE-based pivoting gives you finer control over the logic, lets you apply different transformations to different columns, and makes it easier to mix pivoted and non-pivoted columns in the same query. It’s also just easier to understand for many developers. There’s no special syntax to remember, just standard SQL operations.
Basic Example
Let’s start with a simple example to demonstrate how to create a pivot table without the PIVOT operator.
Sample Data
We’ll use a simple dataset tracking application health checks across different services. This gives us clear categorical data to pivot:
CREATE TABLE ServiceHealth (
CheckDate DATE,
ServiceName VARCHAR(20),
HealthStatus VARCHAR(15)
);
INSERT INTO ServiceHealth (CheckDate, ServiceName, HealthStatus)
VALUES
('2025-01-15', 'Authentication', 'Healthy'),
('2025-01-15', 'Database', 'Healthy'),
('2025-01-15', 'MessageQueue', 'Degraded'),
('2025-01-15', 'Storage', 'Healthy'),
('2025-01-16', 'Authentication', 'Healthy'),
('2025-01-16', 'Database', 'Degraded'),
('2025-01-16', 'MessageQueue', 'Healthy'),
('2025-01-16', 'Storage', 'Healthy'),
('2025-01-17', 'Authentication', 'Healthy'),
('2025-01-17', 'Database', 'Healthy'),
('2025-01-17', 'MessageQueue', 'Healthy'),
('2025-01-17', 'Storage', 'Down');
SELECT * FROM ServiceHealth;So the raw data looks like this:
CheckDate ServiceName HealthStatus
---------------- -------------------- ---------------
2025-01-15 Authentication Healthy
2025-01-15 Database Healthy
2025-01-15 MessageQueue Degraded
2025-01-15 Storage Healthy
2025-01-16 Authentication Healthy
2025-01-16 Database Degraded
2025-01-16 MessageQueue Healthy
2025-01-16 Storage Healthy
2025-01-17 Authentication Healthy
2025-01-17 Database Healthy
2025-01-17 MessageQueue Healthy
2025-01-17 Storage Down
Currently this data has one row per service per check date. We want to pivot it so each date gets one row with separate columns for each service’s status.
Basic Conditional Aggregation
The basic technique is to use CASE statements inside aggregate functions. Each CASE expression checks for a specific value and returns the data when found, otherwise NULL. The aggregate function then collapses those NULLs, leaving just the actual values:
SELECT
CheckDate,
MAX(CASE WHEN ServiceName = 'Authentication' THEN HealthStatus END) AS [Authentication],
MAX(CASE WHEN ServiceName = 'Database' THEN HealthStatus END) AS [Database],
MAX(CASE WHEN ServiceName = 'MessageQueue' THEN HealthStatus END) AS [MessageQueue],
MAX(CASE WHEN ServiceName = 'Storage' THEN HealthStatus END) AS [Storage]
FROM ServiceHealth
GROUP BY CheckDate
ORDER BY CheckDate;Result:
CheckDate Authentication Database MessageQueue Storage
---------------- --------------- --------------- --------------- ---------------
2025-01-15 Healthy Healthy Degraded Healthy
2025-01-16 Healthy Degraded Healthy Healthy
2025-01-17 Healthy Healthy Healthy Down
For each row in the result set, SQL evaluates every CASE statement. When ServiceName matches, it returns the HealthStatus. When it doesn’t match, it returns NULL. The MAX() function then picks the non-NULL value from each group. You could use MIN() instead of MAX() if you prefer. When there’s only one non-NULL value per group, they produce the same result.
The GROUP BY clause is required here. It defines what constitutes a single row in your output. Here we’re grouping by CheckDate, so each date becomes one row with the service statuses spread across columns.
Working with Numeric Data
Conditional aggregation works identically with numbers, though your choice of aggregate function becomes more meaningful if you actually have multiple values to aggregate.
Let’s create a new data set:
CREATE TABLE DailySales (
SaleDate DATE,
Region VARCHAR(15),
Revenue DECIMAL(10,2)
);
INSERT INTO DailySales (SaleDate, Region, Revenue)
VALUES
('2024-01-15', 'North', 15000.00),
('2024-01-15', 'North', 12000.00),
('2024-01-15', 'South', 18000.00),
('2024-01-15', 'East', 14000.00),
('2024-01-16', 'North', 16000.00),
('2024-01-16', 'South', 19000.00),
('2024-01-16', 'East', 15000.00);
SELECT * FROM DailySales;Here’s what the data looks like:
SaleDate Region Revenue
---------------- --------------- ------------
2024-01-15 North 15000.00
2024-01-15 North 12000.00
2024-01-15 South 18000.00
2024-01-15 East 14000.00
2024-01-16 North 16000.00
2024-01-16 South 19000.00
2024-01-16 East 15000.00
Now let’s run a pivot:
SELECT
SaleDate,
SUM(CASE WHEN Region = 'North' THEN Revenue END) AS [North],
SUM(CASE WHEN Region = 'South' THEN Revenue END) AS [South],
SUM(CASE WHEN Region = 'East' THEN Revenue END) AS [East]
FROM DailySales
GROUP BY SaleDate
ORDER BY SaleDate;Result:
SaleDate North South East
---------- ----- ----- -----
2024-01-15 27000 18000 14000
2024-01-16 16000 19000 15000
Notice we’re using SUM() here instead of MAX() because we actually want to total up multiple sales transactions.
This is where conditional aggregation shows its flexibility. You can use the appropriate aggregate function for your data. COUNT() for counting occurrences, AVG() for averages, MIN() or MAX() for minimum or maximum values, or SUM() for totals.
Handling NULL Values
Just like PIVOT, conditional aggregation can produce NULLs when no data exists for a particular combination. You handle these the same way – with COALESCE(), IFNULL(), or ISNULL() (depending on your DBMS) wrapping each expression.
Example:
-- Add a service that's missing from some dates
INSERT INTO ServiceHealth (CheckDate, ServiceName, HealthStatus)
VALUES
('2024-01-18', 'Authentication', 'Healthy'),
('2024-01-18', 'Database', 'Healthy'),
('2024-01-18', 'MessageQueue', 'Healthy');
-- Notice Storage is missing for this date
SELECT
CheckDate,
COALESCE(MAX(CASE WHEN ServiceName = 'Authentication' THEN HealthStatus END), 'Unknown') AS Authentication,
COALESCE(MAX(CASE WHEN ServiceName = 'Database' THEN HealthStatus END), 'Unknown') AS [Database],
COALESCE(MAX(CASE WHEN ServiceName = 'MessageQueue' THEN HealthStatus END), 'Unknown') AS MessageQueue,
COALESCE(MAX(CASE WHEN ServiceName = 'Storage' THEN HealthStatus END), 'Unknown') AS Storage
FROM ServiceHealth
GROUP BY CheckDate
ORDER BY CheckDate;Result:
CheckDate Authentication Database MessageQueue Storage
---------- -------------- -------- ------------ -------
2024-01-18 Healthy Healthy Healthy Unknown
2025-01-15 Healthy Healthy Degraded Healthy
2025-01-16 Healthy Degraded Healthy Healthy
2025-01-17 Healthy Healthy Healthy Down
COALESCE() returns the first non-NULL value in its argument list, so if the CASE statement produces NULL, you get your default value instead. For numeric columns, you’d typically use 0 rather than a text value, though for calculations like averages you may want to leave NULLs as-is to avoid skewing results.
Applying Different Logic to Different Columns
One major advantage of conditional aggregation over PIVOT is that each column can have its own logic. You’re not constrained to the same transformation across all pivoted columns.
SELECT
CheckDate,
MAX(CASE WHEN ServiceName = 'Authentication' THEN HealthStatus END) AS Authentication,
MAX(CASE WHEN ServiceName = 'Database' THEN HealthStatus END) AS [Database],
COUNT(CASE WHEN ServiceName = 'MessageQueue' AND HealthStatus = 'Degraded' THEN 1 END) AS MessageQueueIssues,
MAX(CASE WHEN ServiceName = 'Storage' THEN
CASE
WHEN HealthStatus = 'Healthy' THEN 'OK'
WHEN HealthStatus = 'Degraded' THEN 'Warning'
ELSE 'Critical'
END
END) AS StorageSimplified
FROM ServiceHealth
GROUP BY CheckDate
ORDER BY CheckDate;Result:
CheckDate Authentication Database MessageQueueIssues StorageSimplified
---------- -------------- -------- ------------------ -----------------
2024-01-18 Healthy Healthy 0 null
2025-01-15 Healthy Healthy 1 OK
2025-01-16 Healthy Degraded 0 OK
2025-01-17 Healthy Healthy 0 Critical
Here we’re doing something different for each service. Authentication and Database just show their status. MessageQueue counts how many times it was degraded. Storage maps its status to simplified categories. This kind of column-specific logic would be awkward or impossible with a PIVOT operator.
Multiple Grouping Columns
You can group by multiple columns to create more detailed pivot tables. This produces one row for each unique combination of grouping values.
Example:
CREATE TABLE ProjectHours (
ProjectName VARCHAR(50),
TeamMember VARCHAR(50),
TaskType VARCHAR(50),
Hours DECIMAL(5,2)
);
INSERT INTO ProjectHours (ProjectName, TeamMember, TaskType, Hours)
VALUES
('WebRedesign', 'Aimee', 'Development', 20),
('WebRedesign', 'Aimee', 'Testing', 5),
('WebRedesign', 'Rohit', 'Development', 15),
('WebRedesign', 'Rohit', 'Testing', 8),
('MobileApp', 'Aimee', 'Development', 25),
('MobileApp', 'Aimee', 'Testing', 6),
('MobileApp', 'Rohit', 'Development', 22),
('MobileApp', 'Rohit', 'Testing', 7);
SELECT
ProjectName,
TeamMember,
SUM(CASE WHEN TaskType = 'Development' THEN Hours END) AS Development,
SUM(CASE WHEN TaskType = 'Testing' THEN Hours END) AS Testing
FROM ProjectHours
GROUP BY ProjectName, TeamMember
ORDER BY ProjectName, TeamMember;Result:
ProjectName TeamMember Development Testing
----------- ---------- ----------- -------
MobileApp Aimee 25 6
MobileApp Rohit 22 7
WebRedesign Aimee 20 5
WebRedesign Rohit 15 8
This produces one row per project-team member combination, with hours broken out by task type. The GROUP BY determines the grain of your result set. Everything listed there defines what makes a unique row.
Creating Calculated Columns Alongside Pivoted Data
You can freely mix pivoted columns with calculations, which gives you significant flexibility in report design.
SELECT
SaleDate,
SUM(CASE WHEN Region = 'North' THEN Revenue END) AS North,
SUM(CASE WHEN Region = 'South' THEN Revenue END) AS South,
SUM(CASE WHEN Region = 'East' THEN Revenue END) AS East,
SUM(Revenue) AS TotalRevenue,
CAST(SUM(CASE WHEN Region = 'North' THEN Revenue END) * 100.0 / SUM(Revenue) AS DECIMAL(5,2)) AS NorthPercent
FROM DailySales
GROUP BY SaleDate
ORDER BY SaleDate;Result:
SaleDate North South East TotalRevenue NorthPercent
---------- ----- ----- ----- ------------ ------------
2024-01-15 27000 18000 14000 59000 45.76
2024-01-16 16000 19000 15000 50000 32
Here we’re showing the revenue by region, a total across all regions, and calculating what percentage came from the North region. This kind of mixing of pivoted and aggregate data is usually quite straightforward with conditional aggregation.
Building Dynamic Pivots Without PIVOT
You can build dynamic conditional aggregation queries with the CASE approach, just like you can with the PIVOT operator. To do this, you query the distinct values, build a string of CASE statements, and then execute it dynamically:
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
SELECT @columns = STRING_AGG(
'MAX(CASE WHEN ServiceName = ' + QUOTENAME(ServiceName, '''') +
' THEN HealthStatus END) AS ' + QUOTENAME(ServiceName),
', '
)
FROM (SELECT DISTINCT ServiceName FROM ServiceHealth) AS Services;
SET @sql = N'
SELECT CheckDate, ' + @columns + '
FROM ServiceHealth
GROUP BY CheckDate
ORDER BY CheckDate;';
EXEC sp_executesql @sql;Result:
CheckDate Authentication Database MessageQueue Storage
---------- -------------- -------- ------------ -------
2024-01-18 Healthy Healthy Healthy null
2025-01-15 Healthy Healthy Degraded Healthy
2025-01-16 Healthy Degraded Healthy Healthy
2025-01-17 Healthy Healthy Healthy Down
This constructs a CASE statement for each distinct service name found in the data. The result adapts automatically to whatever services exist, just like dynamic PIVOT. STRING_AGG() builds the comma-separated list of column expressions, and QUOTENAME() (a SQL Server function) handles any special characters in the service names (in other databases, you can use CONCAT() or the || operator to manually wrap names in backticks, double quotes, or square brackets depending on the database).
Cross-Database Compatibility
The beauty of conditional aggregation is that it works everywhere. MySQL, PostgreSQL, Oracle, SQLite, SQL Serve. Basically, any database that supports CASE statements and GROUP BY can pivot data this way. The syntax is identical across platforms, though you may need to replace STRING_AGG() with GROUP_CONCAT(), LISTAGG() or a similar function, depending on your DBMS.
For maximum portability, stick to standard aggregate functions (SUM(), COUNT(), MAX(), MIN(), AVG()) and basic CASE syntax. Avoid database-specific functions unless you’re confident your code won’t need to run elsewhere.