Export Data from SQL Server to Excel and Simulate COMPUTE BY Clause

By: Mircea Dragan   |   Updated: 2021-05-20   |   Comments   |   Related: More > Import and Export

Problem

As you may know, Microsoft has removed the COMPUTE BY clause in the SQL Server SELECT statement, starting with SQL Server 2012. The ROLL UP clause, which is supposed to be a replacement for COMPUTE BY, does not give a similar result.  In the article, Using SQL Server ROLL UP to Get Similar Results of COMPUTE BY, I presented how we can replace the COMPUTE BY clause by using the ROLL UP clause instead.

If we export data to Excel, we can do exactly what COMPUTE BY does. For this tutorial, I will use SQL Server 2019, Visual Studio 2019 and Microsoft Excel 2013. Some of the C# code will not work if you don’t use the latest .NET (minimum 4.8). I will also use the AdventureWorks database.

Solution

The COMPUTE BY clause has some disadvantages. One disadvantage is that it generates a lot of data sets, two data sets for each group of rows for which it does a computation. For example, in Using SQL Server ROLL UP to Get Similar Results of COMPUTE BY we have 6 groups of data rows, and a total of 12 data sets.

Another disadvantage of COMPUTE BY is speed. For the AdventureWorks database I used in this article, the initial example I wanted to use had around 121,000 rows. When I used COMPUTE BY clause in SQL Server Management Studio (SSMS) I had to stop the process after about one hour. Using the same SELECT without COMPUTE BY took around 4 seconds. Having so many rows exported to Excel will create a very large Excel file, so I decided to restrict the selected data, such that there will be only around 1,500 rows. The purpose of this article is to show how we can get exactly the same result that the COMPUTE BY clause gives.

To illustrate how this works, I will use the following SELECT statement:

SELECT 
    SalesOrderID, Quantity, UnitPrice, OrderTotal, [Name]
FROM
(
    SELECT 
        SalesOrderID, 
        productid as productid,
        OrderQty as Quantity,
        UnitPrice as UnitPrice,
        OrderQty*UnitPrice as OrderTotal
    FROM Sales.SalesOrderDetail
    WHERE Year(ModifiedDate) = 2014 and MONTH(ModifiedDate) <= 2 and OrderQty > 1
) sales
JOIN
(
    SELECT 
        productid,
        name
    FROM [Production].[Product]
) product
ON sales.productid = product.ProductID
ORDER BY SalesOrderID, name
--COMPUTE sum(ordertotal) BY salesorderid

I commented the COMPUTE BY clause to illustrate what I want to get: for each order with a minimum of 2 items, issued in the first two months of 2014 we compute its total.

The method used to simulate the behavior of COMPUTE BY is straight forward:

1. Read the first row and keep the salesorderid as the first element in the sum of first group.
2. For each row starting with the second, compare the current salesorderid with the previous one. If it is the same, increase the order total for the current group with the total of the current row.
3. If the salesorderid of the current row is different than the previous one, start a new group.

This is illustrated in the following code:

if(dr.HasRows)
{
    for(int j = 0; j < dr.FieldCount; ++j)
    {
        xlsWorksheet.Cells[i, j + 1] = dr.GetName(j);
    }
    ++i;
    // First read
    dr.Read();
    prevSalesOrderId = dr.GetInt32(0);
    currentSalesOrderId = prevSalesOrderId;
    orderTotal = (double)dr.GetDecimal(3);
    for(int j = 1; j <= dr.FieldCount; ++j)
        xlsWorksheet.Cells[i, j] = dr.GetValue(j - 1);
    ++i;
}

while(dr.Read())
{
    currentSalesOrderId = dr.GetInt32(0);
    if(currentSalesOrderId == prevSalesOrderId)
    {
        orderTotal += (double)dr.GetDecimal(3);
    }
    else
    {
        // We have the first sum
        xlsWorksheet.Cells[i, 4] = orderTotal;
        orderTotal = (double)dr.GetDecimal(3);
        prevSalesOrderId = currentSalesOrderId;
        ++i;
    }
    for(int j = 1; j <= dr.FieldCount; ++j)
        xlsWorksheet.Cells[i, j] = dr.GetValue(j - 1);
    ++i;
}

There are several ways of doing these computations, but in my opinion this approach is simple and doesn’t involve a lot of programming effort. We can also use more complex situations, where we can use several mathematical functions (in this example we only use SUM).

Next Steps

• Download the sample code sample.

About the author

Mircea Dragan is a Senior Computer Scientist with a strong mathematical background with over 18 years of hands-on experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips