PIVOT/UNPIVOT vs Conditional Aggregation and Maintainable Cross-Tab Reporting

Learning outcomes

ServiceHub reporting needs a cross-tab with one row per technician and one column per service status. The first implementation hard-codes a PIVOT, the second builds column names directly from user text, and a third creates a dynamic result whose columns change every week. All three can “work,” yet only one may satisfy a stable API contract. This lesson treats PIVOT as a data-shaping operator—not a substitute for report design—and compares it with conditional aggregation and safe dynamic SQL.

1. Bootstrap a long-form reporting table

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab06') IS NULL EXEC(N'CREATE SCHEMA lab06 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab06.TechnicianMetric;GOCREATE TABLE lab06.TechnicianMetric(  technician_id int NOT NULL,  metric_month date NOT NULL,  status varchar(20) NOT NULL,  ticket_count int NOT NULL,  CONSTRAINT PK_lab06_TechnicianMetric PRIMARY KEY(technician_id,metric_month,status));INSERT lab06.TechnicianMetric VALUES(101,'2026-08-01','open',4),(101,'2026-08-01','closed',9),(101,'2026-08-01','cancelled',1),(102,'2026-08-01','open',2),(102,'2026-08-01','closed',7),(103,'2026-08-01','open',5),(103,'2026-08-01','closed',6);GO

The table has a stable long-form schema: each row identifies technician, month, status, and count. A cross-tab is a presentation of this data, not a different source truth.

2. Static PIVOT makes the rotated columns explicit

PIVOT takes unique values from one input column and turns the selected values into output columns while aggregating a measure. The IN list is part of the query schema, so a static PIVOT is suitable when the consumer contract explicitly names those columns.

USE ServiceHubLab;GOSELECT technician_id,       COALESCE([open],0) AS open_count,       COALESCE([closed],0) AS closed_count,       COALESCE([cancelled],0) AS cancelled_countFROM(  SELECT technician_id, status, ticket_count  FROM lab06.TechnicianMetric  WHERE metric_month='2026-08-01') AS srcPIVOT(  SUM(ticket_count) FOR status IN ([open],[closed],[cancelled])) AS pORDER BY technician_id;GO

Technician 102 has no cancelled row, so the pivoted cancelled value is NULL before the presentation-level COALESCE. That absence is different from a recorded zero unless the domain explicitly treats them as equivalent. The query makes that policy visible instead of allowing a report renderer to guess.

3. Conditional aggregation is often easier when the schema is fixed

The same static cross-tab can be written as aggregate expressions over CASE. This is especially useful when you need several measures, custom conditions, or explicit null/zero policies. Neither syntax is universally faster; compare plans and readability for the actual query.

USE ServiceHubLab;GOSELECT technician_id,       SUM(CASE WHEN status='open' THEN ticket_count ELSE 0 END) AS open_count,       SUM(CASE WHEN status='closed' THEN ticket_count ELSE 0 END) AS closed_count,       SUM(CASE WHEN status='cancelled' THEN ticket_count ELSE 0 END) AS cancelled_count,       SUM(ticket_count) AS total_countFROM lab06.TechnicianMetricWHERE metric_month='2026-08-01'GROUP BY technician_idORDER BY technician_id;GO

Here the total and pivoted measures are readable in one aggregate. PIVOT can be more concise for a single rotated measure; conditional aggregation is often more transparent when the report needs several related expressions. Microsoft also warns that repeated PIVOT/UNPIVOT operators inside one statement can negatively affect performance, so avoid chaining them as a formatting trick.

4. UNPIVOT is a reshape, not a lossless round-trip promise

USE ServiceHubLab;GOWITH Wide AS(  SELECT CAST(101 AS int) technician_id,         CAST(4 AS int) open_count,         CAST(9 AS int) closed_count,         CAST(NULL AS int) cancelled_count)SELECT technician_id, metric_name, metric_valueFROM WideUNPIVOT(  metric_value FOR metric_name IN (open_count,closed_count,cancelled_count)) AS uORDER BY metric_name;GO

Observe the actual result rather than assuming that a PIVOT→UNPIVOT cycle reproduces the original source rows. Missing values, aggregation, duplicate source rows, data-type coercion, and generated column names can change information. Use UNPIVOT when its row-producing semantics are the intended contract.

5. Dynamic PIVOT means dynamic SQL—and identifiers need governance

A dynamic cross-tab must first discover or receive the permitted column names, quote them as identifiers, build a statement, and then execute it. Parameters passed to sp_executesql can protect data values; they cannot stand in for identifiers such as column names. QUOTENAME escapes a validated identifier but does not decide whether the identifier should be allowed. A safe system therefore applies an allowlist/domain rule before quoting.

USE ServiceHubLab;GODECLARE @cols nvarchar(max) = N'[open],[closed],[cancelled]'; -- from trusted metadata/allowlistDECLARE @month date = '2026-08-01';DECLARE @sql nvarchar(max) = N'SELECT technician_id,' + @cols + N'FROM(  SELECT technician_id,status,ticket_count  FROM lab06.TechnicianMetric  WHERE metric_month=@m) AS srcPIVOT (SUM(ticket_count) FOR status IN (' + @cols + N')) AS pORDER BY technician_id;';EXEC sys.sp_executesql @sql, N'@m date', @m=@month;GO

In production, derive @cols from trusted domain metadata and apply QUOTENAME per identifier. Never concatenate raw request text into an identifier list. Even with perfect injection safety, a result whose column set changes can break typed clients; consider returning long-form rows or JSON metadata when the consumer needs arbitrary dimensions.

6. Production judgment and lab cleanup

Prefer static columns for APIs, contracts, exports, and dashboards whose schema should be versioned. Use dynamic cross-tabs for controlled reporting surfaces that explicitly support variable dimensions. Inspect actual plans and logical reads; do not assume PIVOT is faster or slower than CASE by syntax alone. Required permissions are SELECT on the source and ordinary rights to execute any approved dynamic statement. No paid edition, server restart, or cloud dependency is required.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab06.TechnicianMetric;GO

7. Treat the cross-tab as a versioned interface

A production cross-tab often outlives the query that created it. CSV exports, BI semantic models, typed application DTOs, cache keys, and tests can all depend on exact column names and nullability. Therefore adding a new status value to the source domain must not silently add a column to a stable API unless that is an explicit versioned change. Static PIVOT and conditional aggregation make this dependency visible because the contract names the columns in source control.

When a genuinely exploratory report needs dynamic columns, separate three responsibilities: discover allowed domain values, transform those values into safely quoted identifiers, and parameterize ordinary filter values such as date ranges. Log the generated shape for supportability, cap the number of generated columns, and reject identifiers outside the approved domain. This protects both security and operability: even nonmalicious high-cardinality input can create enormous statements/results. If consumers can accept rows instead of columns, a long-form result usually scales and versions more cleanly.

Authoritative references

• PIVOT and UNPIVOT — rotation semantics and repeated-operator performance note
• QUOTENAME — identifier delimiting
• sp_executesql — parameterized execution of dynamic statements
• Execution plans — compare physical strategies with evidence
• SQL Server 2025 build versions — servicing baseline