Day 11: PIVOT & UNPIVOT

A. Lesson overview

Activity breakdown

Business scenario. The board pack needs cross-tab tables: sales by category and quarter, payments by method and year, stock by product and warehouse. Analysts are tired of rewriting the column list every time a new year or warehouse appears, so they also want a version that discovers its columns from the data. Meanwhile, the data team receives a monthly spreadsheet in “wide” format and needs it in “long” format for loading.

B. Theory explained clearly

Part 1 — PIVOT

B1. What a pivot does

A pivot rotates distinct values of one column (the spreading column) into separate output columns, aggregating a value column for each. Data held “long”:

becomes “wide”:

B2. PIVOT syntax

SELECT  <grouping columns>, [Value1], [Value2], …
FROM   (SELECT <grouping columns>, <spreading column>, <value column>
        FROM …) AS src
PIVOT
(
    <aggregate>(<value column>)
    FOR <spreading column> IN ([Value1], [Value2], …)
) AS pvt;

B3. The implicit grouping trap

PIVOT has no GROUP BY. It groups by every column of the source that is neither the spreading column nor the aggregated column. If the source carries an extra column — say OrderID — the result has one row per order instead of one per month. This is the single most common PIVOT bug. Always feed PIVOT from a narrow derived table or CTE that contains exactly: grouping columns + spreading column + value column.

B4. PIVOT or conditional aggregation?

-- PIVOT
SELECT MonthNo, [Online], [Store]
FROM  (SELECT MONTH(o.OrderDate) AS MonthNo, o.SalesChannel, l.Quantity * l.UnitPrice * (1 - l.DiscountPct) AS LineNet
       FROM …) AS src
PIVOT (SUM(LineNet) FOR SalesChannel IN ([Online], [Store])) AS p;

-- Conditional aggregation (Day 2)
SELECT MONTH(o.OrderDate) AS MonthNo,
       SUM(CASE WHEN o.SalesChannel = 'Online' THEN … END) AS Online,
       SUM(CASE WHEN o.SalesChannel = 'Store'  THEN … END) AS Store
FROM … GROUP BY MONTH(o.OrderDate);

Both produce the same execution work in most cases; choose the one your team reads most easily. Dynamic SQL tends to use PIVOT because the generated text is shorter.

Part 2 — UNPIVOT

UNPIVOT rotates columns into rows:

SELECT  <other columns>, <name column>, <value column>
FROM    <source>
UNPIVOT
(
    <value column> FOR <name column> IN ([Col1], [Col2], …)
) AS u;

Rules and traps:

• All unpivoted columns must have the same data type (including length) — CAST them in a derived table first.
• UNPIVOT drops rows whose value is NULL. Unpivoting Email and Phone for 40 customers gives 66 rows, not 80, because 4 emails and 10 phones are NULL.
• UNPIVOT is not an exact inverse of PIVOT: aggregated detail cannot be recovered, and NULL cells disappear.

CROSS APPLY (VALUES (…), (…)) from Day 10 is the flexible alternative: it keeps NULLs (filter them if you want), lets you unpivot several column pairs at once, and allows expressions.

Part 3 — Primer: dynamic SQL for dynamic pivots

PIVOT needs its column list written into the query. If the values come from data (new years, new warehouses), you must build the query text and execute it. That is dynamic SQL.

DECLARE @cols nvarchar(max),
        @sql  nvarchar(max);

-- 1. Build the column list from data, safely quoted
SELECT @cols = STRING_AGG(CAST(QUOTENAME(PaymentMethod) AS nvarchar(max)), N', ')
               WITHIN GROUP (ORDER BY PaymentMethod)
FROM  (SELECT DISTINCT PaymentMethod FROM Sales.Payment) AS m;
-- @cols = [Bank Transfer], [Card], [Gift Card], [PayPal]

-- 2. Build the statement
SET @sql = N'
SELECT PayYear, ' + @cols + N'
FROM  (SELECT YEAR(PaymentDate) AS PayYear, PaymentMethod, Amount
       FROM   Sales.Payment) AS src
PIVOT (SUM(Amount) FOR PaymentMethod IN (' + @cols + N')) AS p
ORDER BY PayYear;';

PRINT @sql;                       -- 3. Always inspect what you built
EXEC sys.sp_executesql @sql;      -- 4. Execute it

Security rule for this course: only identifiers (column names) are concatenated, and only after QUOTENAME. Values — dates, IDs, text typed by a user — are never concatenated; they are passed as sp_executesql parameters. Dynamic SQL also runs in its own scope: variables declared outside it are not visible inside unless passed as parameters.

C. Dataset and starting state

Preparation. None if RetailLab is unchanged. Otherwise run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive) or scripts 00 → 03.

Validation. Expected: 4, 2, 120861.55.

USE RetailLab;
GO

SELECT  (SELECT COUNT(DISTINCT PaymentMethod) FROM Sales.Payment) AS PaymentMethods,
        (SELECT COUNT(*) FROM Inventory.Warehouse)                AS Warehouses,
        (SELECT SUM(Amount) FROM Sales.Payment)                   AS TotalPayments;

D. Worked examples

Worked example 1 — 2025 sales by month and channel

SELECT  MonthNo,
        CAST([Online] AS decimal(12,2)) AS Online,
        CAST([Store]  AS decimal(12,2)) AS Store
FROM   (SELECT  MONTH(o.OrderDate)                               AS MonthNo,
                o.SalesChannel,
                l.Quantity * l.UnitPrice * (1 - l.DiscountPct)   AS LineNet
        FROM    Sales.SalesOrder     AS o
        JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
        WHERE   o.OrderStatus <> 'Cancelled'
          AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01') AS src
PIVOT  (SUM(LineNet) FOR SalesChannel IN ([Online], [Store])) AS p
ORDER BY MonthNo;

Expected result: 12 rows matching Day 2’s lab — January 1321.89 / 2427.68 … December 6097.85 / 3620.80. The source has exactly three columns: MonthNo (grouping), SalesChannel (spreading) and LineNet (value).

Worked example 2 — The implicit grouping trap

SELECT  COUNT(*) AS RowsReturned
FROM   (SELECT  o.OrderID,                                            -- extra column!
                MONTH(o.OrderDate)                               AS MonthNo,
                o.SalesChannel,
                l.Quantity * l.UnitPrice * (1 - l.DiscountPct)   AS LineNet
        FROM    Sales.SalesOrder     AS o
        JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
        WHERE   o.OrderStatus <> 'Cancelled'
          AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01') AS src
PIVOT  (SUM(LineNet) FOR SalesChannel IN ([Online], [Store])) AS p;

Expected result: 198 — one row per order, because OrderID silently became a grouping column. Each row has a value in either Online or Store and NULL in the other. No error warns you.

Worked example 3 — Category by year, with a total column

WITH CategoryTree AS
(
    SELECT CategoryID, CategoryName AS TopCategory FROM Inventory.Category WHERE ParentCategoryID IS NULL
    UNION ALL
    SELECT c.CategoryID, t.TopCategory FROM Inventory.Category AS c JOIN CategoryTree AS t ON c.ParentCategoryID = t.CategoryID
),
src AS
(
    SELECT  t.TopCategory,
            YEAR(o.OrderDate)                               AS OrderYear,
            l.Quantity * l.UnitPrice * (1 - l.DiscountPct)  AS LineNet
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID    = o.OrderID
    JOIN    Inventory.Product    AS p ON p.ProductID  = l.ProductID
    JOIN    CategoryTree         AS t ON t.CategoryID = p.CategoryID
    WHERE   o.OrderStatus <> 'Cancelled'
)
SELECT  TopCategory,
        CAST([2024] AS decimal(12,2))           AS [2024],
        CAST([2025] AS decimal(12,2))           AS [2025],
        CAST([2024] + [2025] AS decimal(12,2))  AS Total
FROM    src
PIVOT  (SUM(LineNet) FOR OrderYear IN ([2024], [2025])) AS p
ORDER BY TopCategory;

Expected result

Numbers become column names by writing them in brackets: [2024]. If any cell were NULL, [2024] + [2025] would be NULL; use COALESCE([2024], 0) + COALESCE([2025], 0) in general.

Worked example 4 — Stock by warehouse (NULL means “not stocked”)

SELECT  ProductCode,
        [1] AS Midlands,
        [2] AS Scotland
FROM   (SELECT p.ProductCode, s.WarehouseID, s.QuantityOnHand
        FROM   Inventory.Stock   AS s
        JOIN   Inventory.Product AS p ON p.ProductID = s.ProductID) AS src
PIVOT  (SUM(QuantityOnHand) FOR WarehouseID IN ([1], [2])) AS p
ORDER BY ProductCode;

Expected result: 29 rows (one per product with any stock record). BPK-001: 18 and 40; BPK-002: 37 and NULL. Here NULL means “no stock record in that warehouse”, which is different from 0 (out of stock) — keep NULL, or label it, but do not blindly turn it into 0.

Worked example 5 — UNPIVOT drops NULLs; APPLY keeps them

-- UNPIVOT
SELECT  COUNT(*) AS UnpivotRows
FROM   (SELECT CustomerID, Email, CAST(Phone AS nvarchar(255)) AS Phone
        FROM   Sales.Customer) AS src
UNPIVOT (ContactValue FOR ContactType IN (Email, Phone)) AS u;

-- CROSS APPLY (VALUES …)
SELECT  COUNT(*) AS ApplyRows,
        COUNT(v.ContactValue) AS ApplyNonNullRows
FROM    Sales.Customer AS c
CROSS APPLY (VALUES (N'Email', c.Email),
                    (N'Phone', CAST(c.Phone AS nvarchar(255)))) AS v (ContactType, ContactValue);

Expected result: 66; then 80 and 66. Without the CAST, the first query fails: The type of column “Phone” conflicts with the type of other columns specified in the UNPIVOT list.

Worked example 6 — A dynamic pivot of payments by method

DECLARE @cols nvarchar(max),
        @sql  nvarchar(max);

SELECT @cols = STRING_AGG(CAST(QUOTENAME(PaymentMethod) AS nvarchar(max)), N', ')
               WITHIN GROUP (ORDER BY PaymentMethod)
FROM  (SELECT DISTINCT PaymentMethod FROM Sales.Payment) AS m;

SET @sql = N'
SELECT  PayYear, ' + @cols + N'
FROM   (SELECT YEAR(PaymentDate) AS PayYear, PaymentMethod, Amount
        FROM   Sales.Payment) AS src
PIVOT  (SUM(Amount) FOR PaymentMethod IN (' + @cols + N')) AS p
ORDER BY PayYear;';

PRINT @sql;
EXEC sys.sp_executesql @sql;

Expected result: the Messages tab shows the generated statement with [Bank Transfer], [Card], [Gift Card], [PayPal]. The result:

If a new payment method appeared in the data, it would become a new column automatically.

E. Guided practical lab — Category × quarter cross-tab, static then dynamic

Finance wants 2025 net sales with top-level categories as rows, quarters as columns, a row total and a grand-total row.

Step 1 — Build and check the narrow source

1. Do: Create a CTE with exactly three columns.
2. Run:

   USE RetailLab;
   GO
   
   WITH CategoryTree AS
   (
       SELECT CategoryID, CategoryName AS TopCategory FROM Inventory.Category WHERE ParentCategoryID IS NULL
       UNION ALL
       SELECT c.CategoryID, t.TopCategory FROM Inventory.Category AS c JOIN CategoryTree AS t ON c.ParentCategoryID = t.CategoryID
   ),
   src AS
   (
       SELECT  t.TopCategory,
               CONCAT('Q', DATEPART(QUARTER, o.OrderDate))      AS Qtr,
               l.Quantity * l.UnitPrice * (1 - l.DiscountPct)   AS LineNet
       FROM    Sales.SalesOrder     AS o
       JOIN    Sales.SalesOrderLine AS l ON l.OrderID    = o.OrderID
       JOIN    Inventory.Product    AS p ON p.ProductID  = l.ProductID
       JOIN    CategoryTree         AS t ON t.CategoryID = p.CategoryID
       WHERE   o.OrderStatus <> 'Cancelled'
         AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
   )
   SELECT COUNT(*) AS SourceRows, CAST(SUM(LineNet) AS decimal(12,2)) AS NetSales FROM src;

3. Observe: 360, 63741.94.
4. Why it matters: The control total is fixed before pivoting.
5. Confirm: matches Day 2.

Step 2 — Static PIVOT

1. Do: Replace the final SELECT.

2. Run:

   SELECT  TopCategory,
           CAST([Q1] AS decimal(12,2)) AS Q1,
           CAST([Q2] AS decimal(12,2)) AS Q2,
           CAST([Q3] AS decimal(12,2)) AS Q3,
           CAST([Q4] AS decimal(12,2)) AS Q4
   FROM    src
   PIVOT  (SUM(LineNet) FOR Qtr IN ([Q1], [Q2], [Q3], [Q4])) AS p
   ORDER BY TopCategory;

3. Observe:

   TopCategory	Q1	Q2	Q3	Q4
   Accessories	2577.33	2355.86	2400.62	4397.27
   Camping	6033.75	7806.15	4897.55	8834.60
   Clothing	5917.38	7895.79	5412.80	5212.83

4. Why it matters: The spreading values are text ('Q1'…), so they become column names directly.

5. Confirm: 3 rows.

Step 3 — Add a row total and a grand-total row

1. Do: Add a Total column with COALESCE, and a total row by feeding the pivot a source that already contains an “All categories” group.
2. Run: replace the final SELECT with:

   , src2 AS
   (
       SELECT TopCategory, Qtr, LineNet FROM src
       UNION ALL
       SELECT N'All categories', Qtr, LineNet FROM src
   )
   SELECT  TopCategory,
           CAST([Q1] AS decimal(12,2)) AS Q1,
           CAST([Q2] AS decimal(12,2)) AS Q2,
           CAST([Q3] AS decimal(12,2)) AS Q3,
           CAST([Q4] AS decimal(12,2)) AS Q4,
           CAST(COALESCE([Q1],0) + COALESCE([Q2],0) + COALESCE([Q3],0) + COALESCE([Q4],0)
                AS decimal(12,2))      AS Total
   FROM    src2
   PIVOT  (SUM(LineNet) FOR Qtr IN ([Q1], [Q2], [Q3], [Q4])) AS p
   ORDER BY CASE WHEN TopCategory = N'All categories' THEN 1 ELSE 0 END, TopCategory;

   (Start with a comma straight after the closing parenthesis of src, and delete the previous final SELECT.)
3. Observe: 4 rows. The last is All categories: 14528.46, 18057.81, 12710.98, 18444.70, 63741.94. The category totals are 11731.09, 27572.05 and 24438.80.
4. Why it matters: Duplicating the source rows under a constant label is a simple way to get a total row from PIVOT. (Alternatively, use conditional aggregation with GROUP BY ROLLUP.)
5. Confirm: the grand total equals the control total.

Step 4 — The dynamic version

1. Do: Rebuild the query as dynamic SQL so the quarter columns come from the data. First build the column list (and the total expression) from the quarters that actually have orders, then embed them in the statement.
2. Run:

   DECLARE @cols  nvarchar(max),
           @total nvarchar(max),
           @sql   nvarchar(max);
   
   SELECT @cols  = STRING_AGG(CAST(QUOTENAME(Qtr) AS nvarchar(max)), N', ') WITHIN GROUP (ORDER BY Qtr),
          @total = STRING_AGG(CAST(N'COALESCE(' + QUOTENAME(Qtr) + N', 0)' AS nvarchar(max)), N' + ') WITHIN GROUP (ORDER BY Qtr)
   FROM  (SELECT DISTINCT CONCAT('Q', DATEPART(QUARTER, OrderDate)) AS Qtr
          FROM   Sales.SalesOrder
          WHERE  OrderStatus <> 'Cancelled'
            AND  OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01') AS q;
   
   SET @sql = N'
   WITH CategoryTree AS
   (
       SELECT CategoryID, CategoryName AS TopCategory FROM Inventory.Category WHERE ParentCategoryID IS NULL
       UNION ALL
       SELECT c.CategoryID, t.TopCategory FROM Inventory.Category AS c JOIN CategoryTree AS t ON c.ParentCategoryID = t.CategoryID
   ),
   src AS
   (
       SELECT  t.TopCategory, CONCAT(''Q'', DATEPART(QUARTER, o.OrderDate)) AS Qtr,
               l.Quantity * l.UnitPrice * (1 - l.DiscountPct) AS LineNet
       FROM    Sales.SalesOrder AS o
       JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
       JOIN    Inventory.Product    AS p ON p.ProductID = l.ProductID
       JOIN    CategoryTree         AS t ON t.CategoryID = p.CategoryID
       WHERE   o.OrderStatus <> ''Cancelled''
         AND   o.OrderDate >= @FromDate AND o.OrderDate < @ToDate
   ),
   src2 AS
   (
       SELECT TopCategory, Qtr, LineNet FROM src
       UNION ALL
       SELECT N''All categories'', Qtr, LineNet FROM src
   )
   SELECT TopCategory, ' + @cols + N', ' + @total + N' AS Total
   FROM   src2
   PIVOT (SUM(LineNet) FOR Qtr IN (' + @cols + N')) AS p
   ORDER BY CASE WHEN TopCategory = N''All categories'' THEN 1 ELSE 0 END, TopCategory;';
   
   PRINT @sql;
   EXEC sys.sp_executesql @sql,
        N'@FromDate date, @ToDate date',
        @FromDate = '2025-01-01', @ToDate = '2026-01-01';

3. Observe: the same 4 rows as step 3 (values unrounded, so they show four decimal places).
4. Why it matters: Three techniques are on display: quoted identifiers built from data (@cols), a generated expression (@total), and values passed as parameters (@FromDate, @ToDate) rather than glued into the text. Single quotes inside the dynamic text are doubled (''Q'').
5. Confirm: the All categories total is 63741.94 (shown as 63741.9400). Change the parameter dates to 2024 and run again — the same code produces the 2024 table.

F. Independent practice

Task 1 — Orders per year by channel (static PIVOT)

Return one row per year with the number of orders (all statuses) in Online and Store columns, plus Total.

Success criteria: 2024: 104, 70, 174. 2025: 114, 88, 202.

Task 2 — Stock by warehouse name

Pivot Inventory.Stock so each product is a row and the columns are the warehouse names — [Midlands Distribution Centre] and [Scotland Hub]. Show — where the product has no stock record in that warehouse.

Success criteria: 29 rows. BPK-002 shows 37 and —; BPK-003 shows 7 and 1.

Task 3 — Unpivot a wide table

Finance sends 2025 quarterly figures in wide format. Simulate it with the static pivot from lab step 2 in a CTE, then UNPIVOT it back to TopCategory, Qtr, NetSales rows.

Success criteria: 12 rows (3 categories × 4 quarters); the NetSales values add up to 63741.94.

Task 4 — Dynamic pivot of stock by warehouse (challenging)

Write dynamic SQL that pivots stock by warehouse name without hard-coding the names. Use QUOTENAME, STRING_AGG … WITHIN GROUP (ORDER BY WarehouseName) and sp_executesql. Print the generated SQL.

Success criteria: the generated IN list is [Midlands Distribution Centre], [Scotland Hub]; the result matches Task 2 (with NULLs instead of —). Inserting a third warehouse (in a test, inside a rolled-back transaction) would add a column without code changes.

Stretch challenge — Years discovered from the data, with growth

Write a dynamic pivot of top-level category × order year (non-cancelled), with years discovered from the data, followed by a Growth% column comparing the last two years present. Build the growth expression dynamically too.

Success criteria: columns [2024], [2025], GrowthPct. Accessories 10440.94 → 11731.09 (+12.4%), Camping 27122.20 → 27572.05 (+1.7%), Clothing 21049.80 → 24438.80 (+16.1%).

G. Solutions and explanations

SELECT  OrderYear,
        [Online],
        [Store],
        [Online] + [Store] AS Total
FROM   (SELECT YEAR(OrderDate) AS OrderYear, SalesChannel, OrderID
        FROM   Sales.SalesOrder) AS src
PIVOT  (COUNT(OrderID) FOR SalesChannel IN ([Online], [Store])) AS p
ORDER BY OrderYear;

SELECT  ProductCode,
        COALESCE(CAST([Midlands Distribution Centre] AS varchar(10)), '—') AS Midlands,
        COALESCE(CAST([Scotland Hub]                 AS varchar(10)), '—') AS Scotland
FROM   (SELECT p.ProductCode, w.WarehouseName, s.QuantityOnHand
        FROM   Inventory.Stock     AS s
        JOIN   Inventory.Product   AS p ON p.ProductID   = s.ProductID
        JOIN   Inventory.Warehouse AS w ON w.WarehouseID = s.WarehouseID) AS src
PIVOT  (SUM(QuantityOnHand) FOR WarehouseName IN ([Midlands Distribution Centre], [Scotland Hub])) AS p
ORDER BY ProductCode;

WITH CategoryTree AS
(
    SELECT CategoryID, CategoryName AS TopCategory FROM Inventory.Category WHERE ParentCategoryID IS NULL
    UNION ALL
    SELECT c.CategoryID, t.TopCategory FROM Inventory.Category AS c JOIN CategoryTree AS t ON c.ParentCategoryID = t.CategoryID
),
src AS
(
    SELECT  t.TopCategory, CONCAT('Q', DATEPART(QUARTER, o.OrderDate)) AS Qtr,
            l.Quantity * l.UnitPrice * (1 - l.DiscountPct)            AS LineNet
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    JOIN    Inventory.Product    AS p ON p.ProductID = l.ProductID
    JOIN    CategoryTree         AS t ON t.CategoryID = p.CategoryID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
),
Wide AS
(
    SELECT  TopCategory,
            CAST([Q1] AS decimal(12,2)) AS Q1, CAST([Q2] AS decimal(12,2)) AS Q2,
            CAST([Q3] AS decimal(12,2)) AS Q3, CAST([Q4] AS decimal(12,2)) AS Q4
    FROM    src
    PIVOT  (SUM(LineNet) FOR Qtr IN ([Q1], [Q2], [Q3], [Q4])) AS p
)
SELECT  TopCategory, Qtr, NetSales
FROM    Wide
UNPIVOT (NetSales FOR Qtr IN (Q1, Q2, Q3, Q4)) AS u
ORDER BY TopCategory, Qtr;

DECLARE @cols nvarchar(max),
        @sql  nvarchar(max);

SELECT @cols = STRING_AGG(CAST(QUOTENAME(WarehouseName) AS nvarchar(max)), N', ')
               WITHIN GROUP (ORDER BY WarehouseName)
FROM   Inventory.Warehouse;

SET @sql = N'
SELECT  ProductCode, ' + @cols + N'
FROM   (SELECT p.ProductCode, w.WarehouseName, s.QuantityOnHand
        FROM   Inventory.Stock     AS s
        JOIN   Inventory.Product   AS p ON p.ProductID   = s.ProductID
        JOIN   Inventory.Warehouse AS w ON w.WarehouseID = s.WarehouseID) AS src
PIVOT  (SUM(QuantityOnHand) FOR WarehouseName IN (' + @cols + N')) AS p
ORDER BY ProductCode;';

PRINT @sql;
EXEC sys.sp_executesql @sql;

BEGIN TRANSACTION;
INSERT INTO Inventory.Warehouse (WarehouseID, WarehouseName, City) VALUES (3, N'Bristol Overflow', N'Bristol');
-- run the dynamic pivot here: a third column appears
ROLLBACK TRANSACTION;   -- the warehouse disappears again

DECLARE @cols nvarchar(max), @prev int, @last int, @growth nvarchar(max), @sql nvarchar(max);

SELECT @cols = STRING_AGG(CAST(QUOTENAME(CAST(OrderYear AS nvarchar(4))) AS nvarchar(max)), N', ')
               WITHIN GROUP (ORDER BY OrderYear)
FROM  (SELECT DISTINCT YEAR(OrderDate) AS OrderYear FROM Sales.SalesOrder WHERE OrderStatus <> 'Cancelled') AS y;

SELECT @last = MAX(OrderYear), @prev = MIN(OrderYear)
FROM  (SELECT DISTINCT TOP (2) YEAR(OrderDate) AS OrderYear
       FROM   Sales.SalesOrder WHERE OrderStatus <> 'Cancelled'
       ORDER BY YEAR(OrderDate) DESC) AS t;

SET @growth = N'CAST(100.0 * (' + QUOTENAME(CAST(@last AS nvarchar(4))) + N' - ' + QUOTENAME(CAST(@prev AS nvarchar(4)))
            + N') / NULLIF(' + QUOTENAME(CAST(@prev AS nvarchar(4))) + N', 0) AS decimal(6,1)) AS GrowthPct';

SET @sql = N'
WITH CategoryTree AS
(
    SELECT CategoryID, CategoryName AS TopCategory FROM Inventory.Category WHERE ParentCategoryID IS NULL
    UNION ALL
    SELECT c.CategoryID, t.TopCategory FROM Inventory.Category AS c JOIN CategoryTree AS t ON c.ParentCategoryID = t.CategoryID
),
src AS
(
    SELECT t.TopCategory, YEAR(o.OrderDate) AS OrderYear, l.Quantity * l.UnitPrice * (1 - l.DiscountPct) AS LineNet
    FROM   Sales.SalesOrder AS o
    JOIN   Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    JOIN   Inventory.Product    AS p ON p.ProductID = l.ProductID
    JOIN   CategoryTree         AS t ON t.CategoryID = p.CategoryID
    WHERE  o.OrderStatus <> ''Cancelled''
)
SELECT TopCategory, ' + @cols + N', ' + @growth + N'
FROM   src
PIVOT (SUM(LineNet) FOR OrderYear IN (' + @cols + N')) AS p
ORDER BY TopCategory;';

PRINT @sql;
EXEC sys.sp_executesql @sql;

H. Knowledge check

1. Understanding. What does PIVOT group by?

2. Query interpretation. A pivot of 2025 sales by channel returns 198 rows instead of 12. What is the most likely cause?

3. Debugging. UNPIVOT (Val FOR Col IN (Email, Phone)) fails with a type-conflict error. Why, and how do you fix it?

4. Understanding. Why does unpivoting Email and Phone for 40 customers return 66 rows, and how could you return all 80?

5. Security. In a dynamic pivot, why must column names be wrapped in QUOTENAME, and why should a user-supplied date not be concatenated into the SQL string?

I. Troubleshooting and cleanup

Cleanup. Day 11 is read-only (the Task 4 test insert is rolled back). Nothing to clean up. Keep RetailLab unchanged for Day 12, which adds a calendar table and a temporal table.

J. Completion checklist, recap and references

Completion checklist

• Worked example 2 returned 198 rows, and you can explain the implicit grouping.
• Worked example 5 returned 66, 80 and 66.
• Your lab cross-tab shows All categories = 63741.94 in both the static and dynamic versions.
• Tasks 1–4 match the success criteria.
• Your dynamic SQL quotes identifiers with QUOTENAME and passes values as parameters.

Recap

• PIVOT turns values of a spreading column into columns with one aggregate; it groups by all remaining source columns — feed it a narrow source.
• Listed values with no data give NULL; unlisted values are ignored.
• Conditional aggregation is an explicit, flexible alternative, especially for several measures or totals.
• UNPIVOT turns columns into rows, requires identical types and drops NULLs; CROSS APPLY (VALUES …) keeps them.
• Dynamic pivots build the column list from data with STRING_AGG + QUOTENAME, run with sp_executesql, and pass values as parameters.

Next: Day 12 — Date and time queries, calendar tables, and system-versioned temporal tables.

References (Microsoft Learn)

• Using PIVOT and UNPIVOT
• STRING_AGG (Transact-SQL)
• QUOTENAME (Transact-SQL)
• sp_executesql (Transact-SQL)
• SQL injection
• Table value constructor (Transact-SQL)