UNPIVOT example

1. Two inventory metrics for each product as rows, rather than columns, so you have a single 'MetricType' column (e.g., 'UnitsInStock', 'UnitsOnOrder') and a single 'MetricValue' column.

SQL Server Query 1

            
 SELECT  

     ProductName,  

     MetricType,  -- New column for the name of the original column (e.g., 'UnitsInStock')  

     MetricValue  -- New column for the value from the original column (e.g., 17, 0)  

 FROM  

     (  

         -- Source data for UNPIVOT  

         SELECT  

             ProductID,  

             ProductName,  

             UnitsInStock,  

             UnitsOnOrder  

         FROM  

             Products  

         -- Optional: Filter products if needed  

         WHERE Discontinued = 0 AND UnitsInStock > 0 AND ProductID < 30  

     ) AS SourceData  

 UNPIVOT  

     (  

         MetricValue FOR MetricType IN (UnitsInStock, UnitsOnOrder) -- Define what to unpivot  

     ) AS UnpivotTable  

 ORDER BY  

     ProductName, MetricType;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("ProductName", "MetricType", "MetricValue")  

     .From(new SqlQueryBuilder().Select()  

         .Columns("ProductID","ProductName","UnitsInStock","UnitsOnOrder")  

         .From("Products")  

         .Where(new Where(new Column("Discontinued").Equale(0))  

             .AND(new Column("UnitsInStock").GreaterThan(0))  

             .AND(new Column("ProductID").LessThan(30))), "SourceTable"  

     )  

     .UnPivot(new UnPivot(new Column("MetricValue"))  

         .For(new Column("MetricType")).IN("UnitsInStock", "UnitsOnOrder")  

         .TableName(new Table("UnpivotTable"))  

     )  

     .OrderBy(new OrderBy().SetColumnAscending("ProductName")  

         .SetColumnAscending("MetricType"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT ProductName,
       MetricType,
       MetricValue
FROM (SELECT ProductID,
             ProductName,
             UnitsInStock,
             UnitsOnOrder
      FROM Products
      WHERE Discontinued = @pMAIN_2507200206539685980
            AND UnitsInStock > @pMAIN_2507200206539685981
            AND ProductID < @pMAIN_2507200206539685982) AS SourceTable UNPIVOT (MetricValue FOR MetricType IN (UnitsInStock, UnitsOnOrder)) AS UnpivotTable
ORDER BY ProductName ASC, MetricType ASC;


            
        

Parameters (If used)

Query Results 1:

2. Prepare the source data with distinct columns for sales and orders

SQL Server Query 2

            
 -- Step 1: Prepare the source data with distinct columns for sales and orders  

 -- This subquery generates the data *before* unpivoting.  

 -- The crucial part is ensuring Sales and Orders are of compatible types.  

 WITH ProductYearlySummary AS (  

     SELECT  

         P.ProductName,  

         CAST(SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount))  AS DECIMAL(18, 2)) AS TotalSales,  

         CAST(COUNT(DISTINCT O.OrderID) AS DECIMAL(18, 2)) AS TotalOrders -- FIX: CAST TotalOrders to DECIMAL  

         -- We cast TotalOrders to DECIMAL(18,2) to match the type of TotalSales,  

         -- assuming TotalSales is also a DECIMAL or MONEY type.  

     FROM  

         [Order Details] AS OD  

     JOIN Products AS P ON OD.ProductID = P.ProductID  

     JOIN Orders AS O ON OD.OrderID = O.OrderID  

     WHERE  

         YEAR(O.OrderDate) = 1997 -- Example: Focus on a single year  

     GROUP BY  

         P.ProductName  

 )  

 -- Step 2: Perform the UNPIVOT operation  

 SELECT  

     ProductName,  

     MetricType, -- Will contain 'TotalSales' or 'TotalOrders'  

     MetricValue -- Will contain the corresponding numeric value  

 FROM  

     ProductYearlySummary  

 UNPIVOT  

     (  

         MetricValue FOR MetricType IN (TotalSales, TotalOrders) -- These must be type-compatible  

     ) AS UnpivotTable  

 ORDER BY  

     ProductName, MetricType;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .WithCTETable(new Table("ProductYearlySummary"), new SqlQueryBuilder().Select()  

         .Column("P.ProductName")  

         .Column(new CAST(new SUM(new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice")  

             .MULTIPLY().StartBracket(1).SUBTRACT(new Column("OD.Discount")).EndBracket()),   

             SqlDataType.DECIMAL, new Tuple<int, int>(18,2)), "TotalSales")  

         .Column(new CAST(new COUNT(new Column("O.OrderID"), true), SqlDataType.DECIMAL, new Tuple<int,int>(18,2)), "TotalOrders")  

         .From("[Order Details]", "OD")  

         .Join(new List<IJoin>()   

         {  

             new INNERJOIN().TableName("Products", "P")  

                 .On(new Column("OD.ProductID").Equale(new Column("P.ProductID"))),  

             new INNERJOIN().TableName("Orders","O")  

                 .On(new Column("OD.OrderID").Equale(new Column("O.OrderID")))  

         })  

         .Where(new Where(new YEAR(new Column("O.OrderDate")).Equale(1997)))  

         .GroupBy(new GroupBy("P.ProductName"))  

     )  

     .Select()  

     .Columns("ProductName", "MetricType", "MetricValue")  

     .From("ProductYearlySummary")  

     .UnPivot(new UnPivot(new Column("MetricValue"))  

         .For(new Column("MetricType"))  

         .IN("TotalSales","TotalOrders")  

         .TableName(new Table("UnpivotTable")))  

     .OrderBy(new OrderBy().SetColumnAscending("ProductName")  

         .SetColumnAscending("MetricType"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
WITH ProductYearlySummary
AS (SELECT P.ProductName,
           CAST (SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200206539776850 - OD.Discount)) AS DECIMAL (18, 2)) AS TotalSales,
           CAST (COUNT(DISTINCT O.OrderID) AS DECIMAL (18, 2)) AS TotalOrders
    FROM [Order Details] AS OD
         INNER JOIN
         Products AS P
         ON OD.ProductID = P.ProductID
         INNER JOIN
         Orders AS O
         ON OD.OrderID = O.OrderID
    WHERE YEAR(O.OrderDate) = @pMAIN_2507200206539776851
    GROUP BY P.ProductName)
SELECT ProductName,
       MetricType,
       MetricValue
FROM ProductYearlySummary UNPIVOT (MetricValue FOR MetricType IN (TotalSales, TotalOrders)) AS UnpivotTable
ORDER BY ProductName ASC, MetricType ASC;


            
        

Parameters (If used)

Query Results 2:

3. UNPIVOTing Already Aggregated Columns (from a subquery/CTE)

SQL Server Query 3

            
 -- UNPIVOTing Already Aggregated Columns (from a subquery/CTE)  

 -- Step 1: Aggregate data in a subquery/CTE  

 WITH SalesAndOrdersByYear AS (  

     SELECT  

         P.ProductName,  

         -- Aggregated columns here:  

         CAST(SUM(CASE WHEN YEAR(O.OrderDate) = 1996 THEN OD.Quantity * OD.UnitPrice END) AS DECIMAL(18,2)) AS Sales1996,  

         CAST(COUNT(DISTINCT CASE WHEN YEAR(O.OrderDate) = 1996 THEN O.OrderID END) AS DECIMAL(18,2)) AS Orders1996,  

         CAST(SUM(CASE WHEN YEAR(O.OrderDate) = 1997 THEN OD.Quantity * OD.UnitPrice END) AS DECIMAL(18,2)) AS Sales1997,  

         CAST(COUNT(DISTINCT CASE WHEN YEAR(O.OrderDate) = 1997 THEN O.OrderID END) AS DECIMAL(18,2)) AS Orders1997  

     FROM  

         Products AS P  

     JOIN [Order Details] AS OD ON P.ProductID = OD.ProductID  

     JOIN Orders AS O ON OD.OrderID = O.OrderID  

     GROUP BY  

         P.ProductName  

 )  

 -- Step 2: UNPIVOT the aggregated columns  

 SELECT  

     ProductName,  

     YearAndMetric, -- Will be like 'Sales1996', 'Orders1996'  

     MetricValue    -- Will contain the actual aggregated number  

 FROM  

     SalesAndOrdersByYear  

 UNPIVOT  

     (  

         MetricValue FOR YearAndMetric IN (  

             Sales1996, -- This was an aggregated column from the CTE  

             Orders1996, -- This was an aggregated column from the CTE  

             Sales1997,  -- This was an aggregated column from the CTE  

             Orders1997  -- This was an aggregated column from the CTE  

         )  

     ) AS UnpivotResult  

 WHERE  

     MetricValue IS NOT NULL -- Exclude products/years with no data  

 ORDER BY  

     ProductName, YearAndMetric;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .WithCTETable(new Table("SalesAndOrdersByYear"), new SqlQueryBuilder()  

         .Select()  

         .Column("P.ProductName")  

         .Column(new CAST(new SUM(new CASE()  

             .When(new YEAR(new Column("O.OrderDate")).Equale(1996))  

                 .Then(new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice")).End()), SqlDataType.DECIMAL,   

                     new Tuple<int, int>(18, 2)), "Sales1996")  

         .Column(new CAST(new COUNT(new CASE()  

             .When(new YEAR(new Column("O.OrderDate")).Equale("1996")).Then(new Column("O.OrderID")).End(), true),  

             SqlDataType.DECIMAL, new Tuple<int,int>(18, 2)), "Orders1996")  

         .Column(new CAST(new SUM(new CASE()  

             .When(new YEAR(new Column("O.OrderDate")).Equale(1997)).Then(  

                 new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice")).End()), SqlDataType.DECIMAL,   

                     new Tuple<int,int>(18, 2)), "Sales1997")  

         .Column(new CAST(new COUNT(new CASE()  

             .When(new YEAR(new Column("O.OrderDate")).Equale(1997)).Then(new Column("O.OrderID")).End(), true),  

             SqlDataType.DECIMAL, new Tuple<int, int>(18, 2)), "Orders1997")  

         .From("Products", "P")  

         .Join(new List<IJoin>()   

         {  

             new INNERJOIN().TableName("[Order Details]", "OD")  

                 .On(new Column("P.ProductID").Equale(new Column("OD.ProductID"))),  

             new INNERJOIN().TableName("Orders", "O")  

                 .On(new Column("OD.OrderID").Equale(new Column("O.OrderID")))  

         })  

         .GroupBy(new GroupBy("P.ProductName"))  

     )  

     .Select()  

     .Columns("ProductName", "YearAndMetric", "MetricValue")  

     .From("SalesAndOrdersByYear")  

     .UnPivot(new UnPivot(new Column("MetricValue"))  

         .For(new Column("YearAndMetric"))  

         .IN("Sales1996","Orders1996","Sales1997","Orders1997")  

         .TableName(new Table("UnpivotResult")))  

     .Where(new Where(new IS_NOT_NULL(new Column("MetricValue"))))  

     .OrderBy(new OrderBy().SetColumnAscending("ProductName")  

         .SetColumnAscending("YearAndMetric"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
WITH SalesAndOrdersByYear
AS (SELECT P.ProductName,
           CAST (SUM(CASE WHEN YEAR(O.OrderDate) = @pMAIN_2507200206539963120 THEN OD.Quantity * OD.UnitPrice END) AS DECIMAL (18, 2)) AS Sales1996,
           CAST (COUNT(DISTINCT CASE WHEN YEAR(O.OrderDate) = @pMAIN_2507200206539963121 THEN O.OrderID END) AS DECIMAL (18, 2)) AS Orders1996,
           CAST (SUM(CASE WHEN YEAR(O.OrderDate) = @pMAIN_2507200206539963122 THEN OD.Quantity * OD.UnitPrice END) AS DECIMAL (18, 2)) AS Sales1997,
           CAST (COUNT(DISTINCT CASE WHEN YEAR(O.OrderDate) = @pMAIN_2507200206539963123 THEN O.OrderID END) AS DECIMAL (18, 2)) AS Orders1997
    FROM Products AS P
         INNER JOIN
         [Order Details] AS OD
         ON P.ProductID = OD.ProductID
         INNER JOIN
         Orders AS O
         ON OD.OrderID = O.OrderID
    GROUP BY P.ProductName)
SELECT ProductName,
       YearAndMetric,
       MetricValue
FROM SalesAndOrdersByYear UNPIVOT (MetricValue FOR YearAndMetric IN (Sales1996, Orders1996, Sales1997, Orders1997)) AS UnpivotResult
WHERE MetricValue IS NOT NULL
ORDER BY ProductName ASC, YearAndMetric ASC;


            
        

Parameters (If used)

Query Results 3:

4. Show current UnitsInStock and UnitsOnOrder for products from specific suppliers as columns, with the supplier name as the main row identifier. This demonstrates UNPIVOTing inventory types, and then PIVOTing on supplier names.

SQL Server Query 4

            
 -- Show current UnitsInStock and UnitsOnOrder for products from specific suppliers as columns, with the supplier name as the main row identifier. This demonstrates UNPIVOTing inventory types, and then PIVOTing on supplier names.  

 SELECT  

     ProductName,  

     InventoryType,  

     [Exotic Liquids] AS Exotic_Liquids, -- Supplier name 1  

     [New Orleans Cajun Delights] AS New_Orleans_Cajun_Delights, -- Supplier name 2  

     [Grandma Kelly''s Homestead] AS Grandma_Kelly_S_Homestead, -- Supplier name 3  

 	[Tokyo Traders] AS Tokyo_Traders  

 FROM  

     (  

         -- Step 1: Select relevant data including supplier name, product name, and inventory metrics  

         SELECT  

             P.ProductName,  

             S.CompanyName AS SupplierName,  

             P.UnitsInStock,  

             P.UnitsOnOrder  

         FROM  

             Products AS P  

         JOIN Suppliers AS S ON P.SupplierID = S.SupplierID  

         WHERE  

             S.CompanyName IN ('Exotic Liquids', 'New Orleans Cajun Delights', 'Grandma Kelly''s Homestead', 'Tokyo Traders')  

     ) AS SourceData  

 UNPIVOT  

     (  

         InventoryValue FOR InventoryType IN (UnitsInStock, UnitsOnOrder) -- Step 2: Unpivot inventory columns  

     ) AS UnpivotedInventory  

 PIVOT  

     (  

         SUM(InventoryValue) -- Step 3: Aggregate (sum) the inventory values  

         FOR SupplierName IN ([Exotic Liquids], [New Orleans Cajun Delights], [Grandma Kelly''s Homestead], [Tokyo Traders]) -- Step 4: Pivot on supplier names  

     ) AS PivotedBySupplier  

 ORDER BY  

     ProductName, InventoryType;  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select()  

     .Columns("ProductName","InventoryType")  

     .PivotedColumn("[Exotic Liquids]", "Exotic_Liquids")  

     .PivotedColumn("[New Orleans Cajun Delights]", "New_Orleans_Cajun_Delights")  

     .PivotedColumn("[Grandma Kelly''s Homestead]", "Grandma_Kelly_S_Homestead")  

     .PivotedColumn("[Tokyo Traders]", "Tokyo_Traders")  

     .From(new SqlQueryBuilder().Select()  

         .Column("P.ProductName")  

         .Column("S.CompanyName", "SupplierName")  

         .Columns("P.UnitsInStock", "P.UnitsOnOrder")  

         .From("Products", "P")  

         .Join(new List<IJoin>()   

         {  

             new INNERJOIN().TableName("Suppliers", "S")  

                 .On(new Column("P.SupplierID").Equale(new Column("S.SupplierID")))  

         })  

         .Where(new Where(new IN(new Column("S.CompanyName"), "Exotic Liquids", "New Orleans Cajun Delights", "Grandma Kelly's Homestead", "Tokyo Traders")))  

     , "SourceData")  

     .UnPivot(new UnPivot(new Column("InventoryValue"))  

         .For(new Column("InventoryType"))  

         .IN("UnitsInStock", "UnitsOnOrder")  

         .TableName(new Table("UnpivotedInventory")))  

     .Pivot(new Pivot(new SUM(new Column("InventoryValue")))  

         .For(new Column("SupplierName"))  

         .IN("[Exotic Liquids]","[New Orleans Cajun Delights]","[Grandma Kelly''s Homestead]","[Tokyo Traders]")  

         .TableName(new Table("PivotedBySupplier")))  

     .OrderBy(new OrderBy()  

         .SetColumnAscending("ProductName")  

         .SetColumnAscending("InventoryType"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT ProductName,
       InventoryType,
       [Exotic Liquids] AS Exotic_Liquids,
       [New Orleans Cajun Delights] AS New_Orleans_Cajun_Delights,
       [Grandma Kelly''s Homestead] AS Grandma_Kelly_S_Homestead,
       [Tokyo Traders] AS Tokyo_Traders
FROM (SELECT P.ProductName,
             S.CompanyName AS SupplierName,
             P.UnitsInStock,
             P.UnitsOnOrder
      FROM Products AS P
           INNER JOIN
           Suppliers AS S
           ON P.SupplierID = S.SupplierID
      WHERE S.CompanyName IN (@pMAIN_2507200206540263240, @pMAIN_2507200206540263241, @pMAIN_2507200206540263242, @pMAIN_2507200206540263243)) AS SourceData UNPIVOT (InventoryValue FOR InventoryType IN (UnitsInStock, UnitsOnOrder)) AS UnpivotedInventory PIVOT (SUM (InventoryValue) FOR SupplierName IN ([Exotic Liquids], [New Orleans Cajun Delights], [Grandma Kelly''s Homestead], [Tokyo Traders])) AS PivotedBySupplier
ORDER BY ProductName ASC, InventoryType ASC;


            
        

Parameters (If used)

Query Results 4:

5. Sales vs. Order Count by Year for Top Product Categories

SQL Server Query 5

            
 -- Sales vs. Order Count by Year for Top Product Categories  

 SELECT  

     CategoryName,  

     MetricType, -- Will be 'TotalSales' or 'TotalOrders'  

     ISNULL([1996], 0.00) AS Year_1996, -- Use ISNULL to show 0 for no data  

     ISNULL([1997], 0.00) AS Year_1997,  

     ISNULL([1998], 0.00) AS Year_1998  

 FROM  

     (  

         -- Step 1: Initial Aggregation (CTE to prepare data for UNPIVOT)  

         -- Calculate TotalSales and TotalOrders for each CategoryName and OrderYear  

         SELECT  

             C.CategoryName,  

             YEAR(O.OrderDate) AS OrderYear,  

             CAST(SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS DECIMAL(18, 2)) AS TotalSales,  

             -- Important: CAST TotalOrders to a compatible type (e.g., DECIMAL)  

             CAST(COUNT(DISTINCT O.OrderID) AS DECIMAL(18, 2)) AS TotalOrders  

         FROM  

             Categories AS C  

         JOIN Products AS P ON C.CategoryID = P.CategoryID  

         JOIN [Order Details] AS OD ON P.ProductID = OD.ProductID  

         JOIN Orders AS O ON OD.OrderID = O.OrderID  

         WHERE  

             YEAR(O.OrderDate) IN (1996, 1997, 1998)  

         GROUP BY  

             C.CategoryName,  

             YEAR(O.OrderDate)  

     ) AS InitialAggregatedData  

 UNPIVOT  

     (  

         MetricValue FOR MetricType IN (TotalSales, TotalOrders) -- Step 2: UNPIVOT operation  

         -- Transforms 'TotalSales' and 'TotalOrders' columns into rows  

         -- MetricValue gets the actual number, MetricType gets 'TotalSales' or 'TotalOrders'  

     ) AS UnpivotedMetrics  

 PIVOT  

     (  

         SUM(MetricValue) -- Step 3: PIVOT operation - Aggregate the MetricValue  

         FOR OrderYear IN ([1996], [1997], [1998]) -- Create columns for these specific years  

     ) AS PivotedByYear  

 ORDER BY  

     CategoryName, MetricType;  

            
        

Create SQL query with SqlQueryBuilder 5

            
 var (sql5, parameters5) = new SqlQueryBuilder()  

     .Select()  

     .Columns("CategoryName","MetricType")  

     .Column(new ISNULL(new Column("[1996]"), 0.00), "Year_1996")  

     .Column(new ISNULL(new Column("[1997]"), 0.00), "Year_1997")  

     .Column(new ISNULL(new Column("[1998]"), 0.00), "Year_1998")  

     .From(new SqlQueryBuilder()  

         .Select()  

         .Column("C.CategoryName")  

         .Column(new YEAR(new Column("O.OrderDate")), "OrderYear")  

         .Column(new CAST(new SUM(new ColumnArithmatic("OD.Quantity")  

             .MULTIPLY("OD.UnitPrice").MULTIPLY().StartBracket(1).SUBTRACT("OD.Discount").EndBracket()),  

             SqlDataType.DECIMAL, new Tuple<int, int>(18, 2)), "TotalSales")  

         .Column(new CAST(new COUNT(new Column("O.OrderID"), true), SqlDataType.DECIMAL, new Tuple<int, int>(18, 2)), "TotalOrders")  

         .From("Categories", "C")  

         .Join(new List<IJoin>()   

         {  

             new INNERJOIN().TableName("Products", "P")  

                 .On(new Column("C.CategoryID").Equale(new Column("P.CategoryID"))),  

             new INNERJOIN().TableName("[Order Details]", "OD")  

                 .On(new Column("P.ProductID").Equale(new Column("OD.ProductID"))),  

             new INNERJOIN().TableName("Orders", "O")  

                 .On(new Column("OD.OrderID").Equale(new Column("O.OrderID")))  

         })  

         .Where(new Where(new IN(new YEAR(new Column("O.OrderDate")), 1996, 1997, 1998)))  

         .GroupBy(new GroupBy(new Column("C.CategoryName"), new YEAR(new Column("O.OrderDate"))))  

     , "InitialAggregatedData")  

     .UnPivot(new UnPivot(new Column("MetricValue"))  

         .For(new Column("MetricType"))  

         .IN("TotalSales", "TotalOrders")  

         .TableName(new Table("UnpivotedMetrics")))  

     .Pivot(new Pivot(new SUM(new Column("MetricValue")))  

         .For(new Column("OrderYear"))  

         .IN("[1996]", "[1997]", "[1998]")  

         .TableName(new Table("PivotedByYear")))  

     .OrderBy(new OrderBy().SetColumnAscending("CategoryName")  

         .SetColumnAscending("MetricType"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT CategoryName,
       MetricType,
       ISNULL([1996], @pMAIN_2507200206540391190) AS Year_1996,
       ISNULL([1997], @pMAIN_2507200206540391191) AS Year_1997,
       ISNULL([1998], @pMAIN_2507200206540391192) AS Year_1998
FROM (SELECT C.CategoryName,
             YEAR(O.OrderDate) AS OrderYear,
             CAST (SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200206540391193 - OD.Discount)) AS DECIMAL (18, 2)) AS TotalSales,
             CAST (COUNT(DISTINCT O.OrderID) AS DECIMAL (18, 2)) AS TotalOrders
      FROM Categories AS C
           INNER JOIN
           Products AS P
           ON C.CategoryID = P.CategoryID
           INNER JOIN
           [Order Details] AS OD
           ON P.ProductID = OD.ProductID
           INNER JOIN
           Orders AS O
           ON OD.OrderID = O.OrderID
      WHERE YEAR(O.OrderDate) IN (@pMAIN_2507200206540391194, @pMAIN_2507200206540391195, @pMAIN_2507200206540391196)
      GROUP BY C.CategoryName, YEAR(O.OrderDate)) AS InitialAggregatedData UNPIVOT (MetricValue FOR MetricType IN (TotalSales, TotalOrders)) AS UnpivotedMetrics PIVOT (SUM (MetricValue) FOR OrderYear IN ([1996], [1997], [1998])) AS PivotedByYear
ORDER BY CategoryName ASC, MetricType ASC;


            
        

Parameters (If used)

Query Results 5: