# 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:

- Read the first row and keep the
**salesorderid**as the first element in the sum of first group. - 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. - 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

##### About the author

**View all my tips**

Article Last Updated: 2021-05-20