STRING_AGG SQL function

1. Usage of STRING_AGG function to show Employees and Their Sales Figures per Quarter (Aggregating OrderIDs).

SQL Server Query 1

            
 SELECT  

     e.FirstName + ' ' + e.LastName AS EmployeeName,  

     YEAR(o.OrderDate) AS SaleYear,  

     DATEPART(quarter, o.OrderDate) AS SaleQuarter,  

     SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalQuarterlySales,  

     STRING_AGG(CAST(o.OrderID AS NVARCHAR(MAX)), ', ') WITHIN GROUP (ORDER BY o.OrderID ASC) AS OrderIDsHandled  

 FROM Employees e  

 JOIN Orders o ON e.EmployeeID = o.EmployeeID  

 JOIN [Order Details] od ON o.OrderID = od.OrderID  

 WHERE YEAR(o.OrderDate) = 1997 -- Filter for a specific year  

 GROUP BY e.FirstName, e.LastName, YEAR(o.OrderDate), DATEPART(quarter, o.OrderDate)  

 ORDER BY EmployeeName, SaleYear, SaleQuarter;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column(new CONCAT(new Column("e.FirstName"), " ", new Column("e.LastName")), "EmployeeName")  

     .Column(new YEAR(new Column("o.OrderDate")), "SaleYear")  

     .Column(new DATEPART(SqlDateInterval.quarter, new Column("o.OrderDate")), "SaleQuarter")  

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

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

     .Column(new STRING_AGG(new CAST(new Column("o.OrderID"), SqlDataType.NVARCHAR,"MAX"), ", ")  

         .WITHIN_GROUP(new OrderBy().SetColumnAscending("o.OrderID")), "OrderIDsHandled")  

     .From("Employees", "e")  

     .Join(new List<IJoin>()   

     {  

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

             .On(new Column("e.EmployeeID").Equale(new Column("o.EmployeeID"))),  

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

             .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

     })  

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

     .GroupBy(new GroupBy(new Column("e.FirstName"),  

         new Column("e.LastName"),  

         new YEAR(new Column("o.OrderDate")),   

         new DATEPART(SqlDateInterval.quarter, new Column("o.OrderDate)"))))  

     .OrderBy(new OrderBy().SetColumnAscending("EmployeeName")  

         .SetColumnAscending("SaleYear")  

         .SetColumnAscending("SaleQuarter"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT CONCAT(e.FirstName, @pMAIN_2505271626463150780, e.LastName) AS EmployeeName, 
                   YEAR(o.OrderDate) AS SaleYear, 
	               DATEPART(quarter, o.OrderDate) AS SaleQuarter, 
	               SUM(od.Quantity  * od.UnitPrice  * (@pMAIN_2505271626463150781 - od.Discount )) AS TotalQuarterlySales, 
	               STRING_AGG(CAST(o.OrderID AS NVARCHAR(MAX)), @pMAIN_2505271626463150782) WITHIN GROUP (ORDER BY o.OrderID ASC ) AS OrderIDsHandled 
            FROM Employees e  
	            INNER JOIN Orders o  ON e.EmployeeID = o.EmployeeID  
	            INNER JOIN [Order Details] od  ON o.OrderID = od.OrderID  
            WHERE YEAR(o.OrderDate) = @pMAIN_2505271626463150783   
            GROUP BY e.FirstName, e.LastName, YEAR(o.OrderDate), DATEPART(quarter, o.OrderDate) 
            ORDER BY EmployeeName ASC,SaleYear ASC,SaleQuarter ASC 
            
        

Parameters (If used)

Query Results 1:

2. Usage of STRING_AGG function to Summarize Customer Order Products (Most Recent Order).

SQL Server Query 2

            
 WITH  CustomerLastOrder  AS  (   

 	SELECT o.CustomerID,   

 		   o.OrderID,   

 		   o.OrderDate,   

 		   ROW_NUMBER() OVER(PARTITION BY o.CustomerID ORDER BY o.OrderDate DESC ) AS rn FROM Orders o  

 ),     

 LastOrderProducts  AS  (   

 	SELECT clo.CustomerID,   

 	       clo.OrderID,   

 		   p.ProductName   

     FROM CustomerLastOrder clo    

 		INNER JOIN [Order Details] od ON clo.OrderID = od.OrderID    

 		INNER JOIN Products p  ON od.ProductID = p.ProductID    

 	WHERE clo.rn = @pMAIN_2505271627038969890     

 )    

 SELECT c.CompanyName AS CompanyName,   

 	   (SELECT TOP 1 OrderDate FROM CustomerLastOrder    

 	    WHERE CustomerID = c.CustomerID    

 		ORDER BY OrderDate DESC ) AS LastOrderDate,   

 		STRING_AGG(lop.ProductName, @pMAIN_2505271627038969891) WITHIN GROUP (ORDER BY lop.ProductName ASC ) AS ProductsInLastOrder   

 FROM Customers c LEFT JOIN LastOrderProducts lop ON c.CustomerID = lop.CustomerID    

 GROUP BY c.CompanyName,c.CustomerID   

 ORDER BY c.CompanyName ASC   

            
        

Create SQL query with SqlQueryBuilder 2

            
 var queryModel2 = new QueryModel();  

   

 var (sql2, parameters2) = new SqlQueryBuilder()  

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

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

         .Column(new ROW_NUMBER().PARTITION_BY(new Column("o.CustomerID"))  

             .ORDER_BY(new OrderBy().SetColumnDescending("o.OrderDate")), "rn")  

         .From("Orders","o")  

     )  

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

         .Columns("clo.CustomerID","clo.OrderID","p.ProductName")  

         .From("CustomerLastOrder","clo")  

         .Join(new List<IJoin>()   

         {  

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

                 .On(new Column("clo.OrderID").Equale(new Column("od.OrderID"))),  

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

                 .On(new Column("od.ProductID").Equale(new Column("p.ProductID")))  

         })  

         .Where(new Where(new Column("clo.rn").Equale(1)))  

     )  

     .Select()  

     .Column("c.CompanyName", "CompanyName")  

     .Column(new SqlQueryBuilder().Select().Top(1).Column("OrderDate", "OrderDate")  

         .From("CustomerLastOrder")  

         .Where(new Where(new Column("CustomerID").Equale(new Column("c.CustomerID"))))  

         .OrderBy(new OrderBy().SetColumnDescending("OrderDate")), "LastOrderDate")  

     .Column(new STRING_AGG(new Column("lop.ProductName"), ", ").WITHIN_GROUP(new OrderBy().SetColumnAscending("lop.ProductName")), "ProductsInLastOrder")  

     .From("Customers", "c")  

     .Join(new List<IJoin>()   

     {  

         new LEFTJOIN().TableName("LastOrderProducts","lop")  

             .On(new Column("c.CustomerID").Equale(new Column("lop.CustomerID")))  

     })  

     .GroupBy(new GroupBy(new Column("c.CompanyName"), new Column("c.CustomerID")))  

     .OrderBy(new OrderBy().SetColumnAscending("c.CompanyName"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
WITH  CustomerLastOrder  AS  ( 
	            SELECT o.CustomerID, 
		               o.OrderID, 
		               o.OrderDate, 
		               ROW_NUMBER() OVER(PARTITION BY o.CustomerID ORDER BY o.OrderDate DESC ) AS rn FROM Orders o
            ),   
            LastOrderProducts  AS  ( 
	            SELECT clo.CustomerID, 
	                   clo.OrderID, 
		               p.ProductName 
                FROM CustomerLastOrder clo  
		            INNER JOIN [Order Details] od ON clo.OrderID = od.OrderID  
		            INNER JOIN Products p  ON od.ProductID = p.ProductID  
	            WHERE clo.rn = @pMAIN_2505271627038969890   
            )  
            SELECT c.CompanyName AS CompanyName, 
	               (SELECT TOP 1 OrderDate FROM CustomerLastOrder  
	                WHERE CustomerID = c.CustomerID  
		            ORDER BY OrderDate DESC ) AS LastOrderDate, 
		            STRING_AGG(lop.ProductName, @pMAIN_2505271627038969891) WITHIN GROUP (ORDER BY lop.ProductName ASC ) AS ProductsInLastOrder 
            FROM Customers c LEFT JOIN LastOrderProducts lop ON c.CustomerID = lop.CustomerID  
            GROUP BY c.CompanyName,c.CustomerID 
            ORDER BY c.CompanyName ASC 
            
        

Parameters (If used)

Query Results 2: