PIVOT example

1. Show the total quantity ordered for each product for specific customers ('ALFKI', 'BLAUS', 'FRANS'), with customer IDs as columns.

SQL Server Query 1

            
 SELECT  

     ProductName,  

     [ALFKI],  

     [BLAUS],  

     [FRANS]  

 FROM  

     (  

         SELECT  

             P.ProductName,  

             C.CustomerID,  

             OD.Quantity  

         FROM  

             [Order Details] AS OD  

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

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

         JOIN Customers AS C ON O.CustomerID = C.CustomerID  

         WHERE C.CustomerID IN ('ALFKI', 'BLAUS', 'FRANS')  

     ) AS SourceTable  

 PIVOT  

     (  

         SUM(Quantity)  

         FOR CustomerID IN ([ALFKI], [BLAUS], [FRANS])  

     ) AS PivotTable  

 ORDER BY  

     ProductName;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column("ProductName")  

     .PivotedColumn("[ALFKI]")  

     .PivotedColumn("[BLAUS]")  

     .PivotedColumn("[FRANS]")  

     .From(new SqlQueryBuilder().Select()  

         .Columns("P.ProductName","C.CustomerID","OD.Quantity")  

         .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"))),  

             new INNERJOIN().TableName("Customers", "C")  

                 .On(new Column("O.CustomerID").Equale(new Column("C.CustomerID")))  

         })  

         .Where(new Where(new IN(new Column("C.CustomerID"), "ALFKI","BLAUS","FRANS"))), "SourceTable"  

     )  

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

         .For(new Column("CustomerID")).IN("[ALFKI]","[BLAUS]","[FRANS]")  

         .TableName(new Table("PivotTable"))  

     )  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT ProductName,
       [ALFKI],
       [BLAUS],
       [FRANS]
FROM (SELECT P.ProductName,
             C.CustomerID,
             OD.Quantity
      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
           INNER JOIN
           Customers AS C
           ON O.CustomerID = C.CustomerID
      WHERE C.CustomerID IN (@pMAIN_2507200208304276340, @pMAIN_2507200208304276341, @pMAIN_2507200208304276342)) AS SourceTable PIVOT (SUM (Quantity) FOR CustomerID IN ([ALFKI], [BLAUS], [FRANS])) AS PivotTable
ORDER BY ProductName ASC;


            
        

Parameters (If used)

Query Results 1:

2. Pivoting Multiple Aggregations (Simplified & Advanced)

SQL Server Query 2

            
 SELECT  

     ProductName,  

     ISNULL([Sales_1996], 0) AS [Sales_1996],  

     ISNULL([Orders_1996], 0) AS [Orders_1996],  

     ISNULL([Sales_1997], 0) AS [Sales_1997],  

     ISNULL([Orders_1997], 0) AS [Orders_1997],  

     ISNULL([Sales_1998], 0) AS [Sales_1998],  

     ISNULL([Orders_1998], 0) AS [Orders_1998]  

 FROM  

     (  

         SELECT  

             P.ProductName,  

             'Sales_' + CAST(YEAR(O.OrderDate) AS NVARCHAR(4))   AS MetricName,  

             SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS MetricValue  

         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) IN (1996, 1997, 1998)  

         GROUP BY  

             P.ProductName,  

             YEAR(O.OrderDate)  

   

         UNION ALL -- Union with the order counts  

   

         SELECT  

             P.ProductName,  

             'Orders_' + CAST(YEAR(O.OrderDate) AS NVARCHAR(4)) AS MetricName,  

             CAST(COUNT(DISTINCT O.OrderID) AS DECIMAL(18,2)) AS MetricValue -- Cast to match sales 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) IN (1996, 1997, 1998)  

         GROUP BY  

             P.ProductName,  

             YEAR(O.OrderDate)  

     ) AS SourceData  

 PIVOT  

     (  

         SUM(MetricValue) -- The aggregate function for the pivoted values  

         FOR MetricName IN ([Sales_1996], [Orders_1996], [Sales_1997], [Orders_1997], [Sales_1998], [Orders_1998])  

     ) AS PivotTable  

 ORDER BY  

     ProductName;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column("ProductName")  

     .Column(new ISNULL(new Column("[Sales_1996]"), 0), "[Sales_1996]")  

     .Column(new ISNULL(new Column("[Orders_1996]"), 0), "[Orders_1996]")  

     .Column(new ISNULL(new Column("[Sales_1997]"), 0), "[Sales_1997]")  

     .Column(new ISNULL(new Column("[Orders_1997]"), 0), "[Orders_1997]")  

     .Column(new ISNULL(new Column("[Sales_1998]"), 0), "[Sales_1998]")  

     .Column(new ISNULL(new Column("[Orders_1998]"), 0), "[Orders_1998]")  

     .From(new SqlQueryBuilder().Select()  

         .Column("P.ProductName")  

         .Column(new CONCAT("Sales_", new CAST(new YEAR(new Column("O.OrderDate")), SqlDataType.NVARCHAR, "4")), "MetricName")  

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

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

         .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 IN(new YEAR(new Column("O.OrderDate")), 1996, 1997, 1998)))  

         .GroupBy(new GroupBy(new Column("P.ProductName"), new YEAR(new Column("O.OrderDate"))))  

         .UnionAll(new SqlQueryBuilder().Select()  

             .Column("P.ProductName")  

             .Column(new CONCAT("Orders_", new CAST(new YEAR(new Column("O.OrderDate")), SqlDataType.NVARCHAR, "4")), "MetricName")  

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

             .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 IN(new YEAR(new Column("O.OrderDate")), 1996, 1997, 1998)))  

             .GroupBy(new GroupBy(new Column("P.ProductName"), new YEAR(new Column("O.OrderDate"))))  

         ), "SourceData")  

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

         .For(new Column("MetricName"))  

         .IN("[Sales_1996]","[Orders_1996]","[Sales_1997]","[Orders_1997]","[Sales_1998]","[Orders_1998]")  

         .TableName(new Table("PivotTable")))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT ProductName,
       ISNULL([Sales_1996], @pMAIN_2507200208304451150) AS [Sales_1996],
       ISNULL([Orders_1996], @pMAIN_2507200208304451151) AS [Orders_1996],
       ISNULL([Sales_1997], @pMAIN_2507200208304451152) AS [Sales_1997],
       ISNULL([Orders_1997], @pMAIN_2507200208304451153) AS [Orders_1997],
       ISNULL([Sales_1998], @pMAIN_2507200208304451154) AS [Sales_1998],
       ISNULL([Orders_1998], @pMAIN_2507200208304451155) AS [Orders_1998]
FROM (SELECT P.ProductName,
             CONCAT(@pMAIN_2507200208304451156, CAST (YEAR(O.OrderDate) AS NVARCHAR (4))) AS MetricName,
             SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200208304451157 - OD.Discount)) AS MetricValue
      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) IN (@pMAIN_2507200208304451158, @pMAIN_2507200208304451159, @pMAIN_250720020830445115_10)
      GROUP BY P.ProductName, YEAR(O.OrderDate)
      UNION ALL
      SELECT P.ProductName,
             CONCAT(@pMAIN_250720020830445115_11, CAST (YEAR(O.OrderDate) AS NVARCHAR (4))) AS MetricName,
             CAST (COUNT(DISTINCT O.OrderID) AS DECIMAL (18, 2)) AS MetricValue
      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) IN (@pMAIN_250720020830445115_12, @pMAIN_250720020830445115_13, @pMAIN_250720020830445115_14)
      GROUP BY P.ProductName, YEAR(O.OrderDate)) AS SourceData PIVOT (SUM (MetricValue) FOR MetricName IN ([Sales_1996], [Orders_1996], [Sales_1997], [Orders_1997], [Sales_1998], [Orders_1998])) AS PivotTable
ORDER BY ProductName ASC;


            
        

Parameters (If used)

Query Results 2:

3. Pivoting Multiple Aggregations (Simplified & Advanced)

SQL Server Query 3

            
 SELECT  

     CategoryName,  

     MetricType,  

     [1996] AS _1996,  

     [1997] AS _1997,  

     [1998] AS _1998  

 FROM  

     (  

         -- Subquery to prepare data for PIVOT, combining multiple aggregates  

         -- First part: Total Sales  

         SELECT  

             C.CategoryName,  

             YEAR(O.OrderDate) AS OrderYear,  

             'TotalSales' AS MetricType,  

             SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS MetricValue  

         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)  

   

         UNION ALL  

   

         -- Second part: Total Orders (for a specific year)  

         SELECT  

             C.CategoryName,  

             YEAR(O.OrderDate) AS OrderYear,  

             'TotalOrders' AS MetricType,  

             CAST(COUNT(DISTINCT O.OrderID) AS DECIMAL(18, 2)) AS MetricValue -- Cast to match TotalSales' data type  

         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 SourceData  

 PIVOT  

     (  

         SUM(MetricValue) -- Aggregate the combined MetricValue  

         FOR OrderYear IN ([1996], [1997], [1998])  

     ) AS PivotTable  

 ORDER BY  

     CategoryName, MetricType;  

   

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Columns("CategoryName","MetricType")  

     .PivotedColumn("[1996]", "_1996")  

     .PivotedColumn("[1997]", "_1997")  

     .PivotedColumn("[1998]", "_1998")  

     .From(new SqlQueryBuilder().Select()  

         .Column("C.CategoryName")  

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

         .ColumnWithValue("TotalSales","MetricType")  

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

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

         .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"))))  

         .UnionAll(new SqlQueryBuilder().Select()  

             .Column("C.CategoryName")  

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

             .ColumnWithValue("TotalOrders", "MetricType")  

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

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

             .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")))))  

     , "SourceData")  

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

         .For(new Column("OrderYear"))  

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

         .TableName(new Table("PivotTable")))  

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

         .SetColumnAscending("MetricType"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT CategoryName,
       MetricType,
       [1996] AS _1996,
       [1997] AS _1997,
       [1998] AS _1998
FROM (SELECT C.CategoryName,
             YEAR(O.OrderDate) AS OrderYear,
             @pMAIN_2507200208304766170 AS MetricType,
             SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200208304766171 - OD.Discount)) AS MetricValue
      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_2507200208304766172, @pMAIN_2507200208304766173, @pMAIN_2507200208304766174)
      GROUP BY C.CategoryName, YEAR(O.OrderDate)
      UNION ALL
      SELECT C.CategoryName,
             YEAR(O.OrderDate) AS OrderYear,
             @pMAIN_2507200208304766175 AS MetricType,
             SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200208304766176 - OD.Discount)) AS MetricValue
      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_2507200208304766177, @pMAIN_2507200208304766178, @pMAIN_2507200208304766179)
      GROUP BY C.CategoryName, YEAR(O.OrderDate)) AS SourceData PIVOT (SUM (MetricValue) FOR OrderYear IN ([1996], [1997], [1998])) AS PivotTable
ORDER BY CategoryName ASC, MetricType ASC;


            
        

Parameters (If used)

Query Results 3:

4. Top-Selling Products by Category, Filtered by Date & Minimum Sales

SQL Server Query 4

            
 -- Parameters for pagination and filtering  

 DECLARE @PageNumber INT = 2; -- Example: Get the 2nd page  

 DECLARE @PageSize INT = 5;   -- Example: 5 products per page  

 DECLARE @TargetYear INT = 1997; -- Example: Orders from 1997  

 DECLARE @MinCategorySalesValue DECIMAL(18, 2) = 50.00; -- Example: Minimum total sales for a category  

   

 -- Calculate the number of rows to skip  

 DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;  

   

 SELECT  

     Cat.CategoryName,  

     P.ProductName,  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS ProductTotalSales,  

     COUNT(DISTINCT O.OrderID) AS NumberOfOrders  

 FROM  

     Categories AS Cat  

 JOIN  

     Products AS P ON Cat.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) = @TargetYear -- Filter orders by year (BEFORE grouping)  

 GROUP BY  

     Cat.CategoryName,  

     P.ProductName  

 HAVING  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) >= @MinCategorySalesValue -- Filter groups by total sales  

 ORDER BY  

     ProductTotalSales DESC, -- Sort by product sales (highest first)  

     Cat.CategoryName ASC,   -- Tie-breaker 1  

     P.ProductName ASC       -- Tie-breaker 2 (ensures unique, consistent order)  

 OFFSET @OffsetRows ROWS  

 FETCH NEXT @PageSize ROWS ONLY;  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select()  

     .Columns("Cat.CategoryName", "P.ProductName")  

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

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

     .Column(new COUNT(new Column("O.OrderID"), true), "NumberOfOrders")  

     .From("Categories", "Cat")  

     .Join(new List<IJoin>()  

     {  

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

             .On(new Column("Cat.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 YEAR(new Column("O.OrderDate")).Equale(1997)))  

     .GroupBy(new GroupBy("Cat.CategoryName", "P.ProductName"))  

     .Having(new Having(new SUM(  

             new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice").MULTIPLY()  

                 .StartBracket(1).SUBTRACT("OD.Discount").EndBracket()).GreaterThanOrEqualeTo(50.00)))  

     .OrderBy(new OrderBy().SetColumnDescending("ProductTotalSales")  

         .SetColumnAscending("Cat.CategoryName").Offset(5,5))  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT Cat.CategoryName,
       P.ProductName,
       SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200208305110540 - OD.Discount)) AS ProductTotalSales,
       COUNT(DISTINCT O.OrderID) AS NumberOfOrders
FROM Categories AS Cat
     INNER JOIN
     Products AS P
     ON Cat.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) = @pMAIN_2507200208305110541
GROUP BY Cat.CategoryName, P.ProductName
HAVING SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200208305110542 - OD.Discount)) >= @pMAIN_2507200208305110543
ORDER BY ProductTotalSales DESC, Cat.CategoryName ASC
OFFSET @pMAIN_2507200208305110544 ROWS FETCH NEXT @pMAIN_2507200208305110545 ROWS ONLY;


            
        

Parameters (If used)

Query Results 4:

5. Search Functionality with Pagination

SQL Server Query 5

            
 -- Parameters for pagination and search  

 DECLARE @PageNumber INT = 1;  

 DECLARE @PageSize INT = 10;  

 DECLARE @SearchProductName NVARCHAR(40) = 'Chai'; -- Example: Search for 'Chai' or 'Cha' or 'syrup'  

   

 -- Calculate the number of rows to skip  

 DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;  

   

 SELECT  

     O.OrderID,  

     O.OrderDate,  

     C.CompanyName,  

     P.ProductName,  

     OD.Quantity,  

     OD.UnitPrice  

 FROM  

     Orders AS O  

 JOIN  

     [Order Details] AS OD ON O.OrderID = OD.OrderID  

 JOIN  

     Products AS P ON OD.ProductID = P.ProductID  

 JOIN  

     Customers AS C ON O.CustomerID = C.CustomerID  

 WHERE  

     P.ProductName LIKE '%' + @SearchProductName + '%' -- Search condition  

 ORDER BY  

     O.OrderDate DESC,   -- Sort by order date  

     O.OrderID DESC,     -- Tie-breaker for orders  

     P.ProductName ASC   -- Tie-breaker for products within an order  

 OFFSET @OffsetRows ROWS  

 FETCH NEXT @PageSize ROWS ONLY;  

            
        

Create SQL query with SqlQueryBuilder 5

            
 var (sql5, parameters5) = new SqlQueryBuilder()  

     .Select()  

     .Columns("O.OrderID","O.OrderDate","C.CompanyName","P.ProductName","OD.Quantity","OD.UnitPrice")  

     .From("Orders", "O")  

     .Join(new List<IJoin>()  

     {  

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

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

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

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

         new INNERJOIN().TableName("Customers", "C")  

             .On(new Column("O.CustomerID").Equale(new Column("C.CustomerID")))  

     })  

     .Where(new Where(new LIKE(new Column("P.ProductName"), "%chai%")))  

     .OrderBy(new OrderBy().SetColumnDescending("O.OrderDate")  

         .SetColumnDescending("O.OrderID")  

         .SetColumnAscending("P.ProductName").Offset(0, 10))  

     .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT O.OrderID,
       O.OrderDate,
       C.CompanyName,
       P.ProductName,
       OD.Quantity,
       OD.UnitPrice
FROM Orders AS O
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
     INNER JOIN
     Products AS P
     ON OD.ProductID = P.ProductID
     INNER JOIN
     Customers AS C
     ON O.CustomerID = C.CustomerID
WHERE P.ProductName LIKE @pMAIN_2507200208305327640
ORDER BY O.OrderDate DESC, O.OrderID DESC, P.ProductName ASC
OFFSET @pMAIN_2507200208305327641 ROWS FETCH NEXT @pMAIN_2507200208305327642 ROWS ONLY;


            
        

Parameters (If used)

Query Results 5:

6. Pagination with Total Row Count in a Single Query

SQL Server Query 6

            
 -- Parameters for pagination  

 DECLARE @PageNumber INT = 2;  

 DECLARE @PageSize INT = 5;  

   

 -- Calculate the number of rows to skip  

 DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;  

   

 SELECT  

     OrderID,  

     CustomerID,  

     OrderDate,  

     Freight,  

     -- Window function to get the total count of rows without pagination  

     COUNT(*) OVER() AS TotalRowCount  

 FROM  

     Orders  

 WHERE  

     OrderDate >= '1997-01-01' -- Example filter  

 ORDER BY  

     OrderDate ASC,  

     OrderID ASC  

 OFFSET @OffsetRows ROWS  

 FETCH NEXT @PageSize ROWS ONLY;  

            
        

Create SQL query with SqlQueryBuilder 6

            
 var (sql6, parameters6) = new SqlQueryBuilder()  

     .Select()  

     .Columns("OrderID","CustomerID","OrderDate","Freight")  

     .Column(new COUNT(new Column("OrderID")).OVER(new OVER()), "TotalRowCount")  

     .From("Orders", "O")  

     .Where(new Where(new Column("OrderDate").GreaterThanOrEqualeTo("1997-01-01")))  

     .OrderBy(new OrderBy().SetColumnAscending("OrderDate")  

         .SetColumnAscending("OrderID").Offset(5, 5))  

     .Build();  

            
        

Query build by SqlQueryBuilder 6

            
SELECT OrderID,
       CustomerID,
       OrderDate,
       Freight,
       COUNT(OrderID) OVER () AS TotalRowCount
FROM Orders AS O
WHERE OrderDate >= @pMAIN_2507200208305415960
ORDER BY OrderDate ASC, OrderID ASC
OFFSET @pMAIN_2507200208305415961 ROWS FETCH NEXT @pMAIN_2507200208305415962 ROWS ONLY;


            
        

Parameters (If used)

Query Results 6:

7. For each order, find the details of the previous order placed by the same customer, sorted by OrderDate

SQL Server Query 7

            
 SELECT  

     O.OrderID,  

     O.CustomerID,  

     O.OrderDate,  

     LAG(O.OrderID) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate) AS PreviousOrderID,  

     LAG(O.OrderDate) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate) AS PreviousOrderDate,  

     LAG(SUM(OD.Quantity * OD.UnitPrice)) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate) AS PreviousOrderTotal  

 FROM  

     Orders AS O  

 JOIN  

     [Order Details] AS OD ON O.OrderID = OD.OrderID  

 GROUP BY  

     O.OrderID, O.CustomerID, O.OrderDate -- Group by order details before window function  

 ORDER BY  

     O.CustomerID, O.OrderDate  

 OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY  

            
        

Create SQL query with SqlQueryBuilder 7

            
 var (sql7, parameters7) = new SqlQueryBuilder()  

     .Select()  

     .Columns("O.OrderID","O.CustomerID","O.OrderDate")  

     .Column(new LAG(new Column("O.OrderID")).PARTITION_BY(new Column("O.CustomerID"))  

         .ORDER_BY(new OrderBy().SetColumnAscending("O.OrderDate")), "PreviousOrderID")  

     .Column(new LAG(new Column("O.OrderDate")).PARTITION_BY(new Column("O.CustomerID"))  

         .ORDER_BY(new OrderBy().SetColumnAscending("O.OrderDate")), "PreviousOrderDate")  

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

         .PARTITION_BY(new Column("O.CustomerID"))  

         .ORDER_BY(new OrderBy().SetColumnAscending("O.OrderDate")), "PreviousOrderTotal")  

     .From("Orders", "O")  

     .Join(new List<IJoin>()  

     {  

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

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

     })  

     .GroupBy(new GroupBy("O.OrderID", "O.CustomerID", "O.OrderDate"))  

     .OrderBy(new OrderBy().SetColumnAscending("O.CustomerID")  

         .SetColumnAscending("O.OrderDate").Offset(10, 10))  

     .Build();  

            
        

Query build by SqlQueryBuilder 7

            
SELECT O.OrderID,
       O.CustomerID,
       O.OrderDate,
       LAG(O.OrderID, @pMAIN_2507200208305468750) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate ASC) AS PreviousOrderID,
       LAG(O.OrderDate, @pMAIN_2507200208305468751) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate ASC) AS PreviousOrderDate,
       LAG(SUM(OD.Quantity * OD.UnitPrice), @pMAIN_2507200208305468752) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate ASC) AS PreviousOrderTotal
FROM Orders AS O
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
GROUP BY O.OrderID, O.CustomerID, O.OrderDate
ORDER BY O.CustomerID ASC, O.OrderDate ASC
OFFSET @pMAIN_2507200208305468753 ROWS FETCH NEXT @pMAIN_2507200208305468754 ROWS ONLY;


            
        

Parameters (If used)

Query Results 7: