I have a stored procedure that returns a CustomerID from SAP and joins with a SQL Server 2000 table. With the count (return_cnt) involved, the grouping must also contain CustomerID and Regio. I have to return the CustomerID to the DataTable in order to get the Regio from SAP, but when I do that, states (Regio) appear separately with their respective counts because the CustomerID is different yet belongs to the same global group. Since these are all part of the same global group, I need to be able to then group the results from the stored proc by state.

How do I take the results in this DataTable, ignore the CustomerID column and re-group the results by Regio?

CODE:

public DataTable getReturnsByLocation(string global_name, string place_id, string model_id, string start_date, string end_date, bool exclude_npf_abuse, string rc_ctry, CustomersFromSAP oCustomersFromSAP, string[] asCustomerIDs)
        {
            ArrayList alCustomerIDLists = ConvertToCustomerIDLists(asCustomerIDs);

            SqlConnection cn = new SqlConnection(connString);
            cn.Open();
            SqlCommand cmd = new SqlCommand("getReturnsByLocation", cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@place_id", place_id));
            cmd.Parameters.Add(new SqlParameter("@model_id", model_id));
            cmd.Parameters.Add(new SqlParameter("@start_date", start_date));
            cmd.Parameters.Add(new SqlParameter("@end_date", end_date));
            cmd.Parameters.Add(new SqlParameter("@exclude_npf_abuse", exclude_npf_abuse));
            cmd.Parameters.Add(new SqlParameter("@rc_ctry", rc_ctry));
            for (int iIndex = 0; iIndex < alCustomerIDLists.Count; iIndex++)
            {
                cmd.Parameters.Add(new SqlParameter("@psCustomerIDs" + (iIndex + 1), SqlDbType.VarChar, 8000)).Value = alCustomerIDLists[iIndex];
            }

            DataSet ds = new DataSet();
            da.Fill(ds, "tablex");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                DataRow drCustomerInfo = oCustomersFromSAP.GetCustomerInfo((string)row["CustomerID"]);
                row["Regio"] = drCustomerInfo["Regio"];
            }
            ds.Tables[0].AcceptChanges();
            ds.Tables[0].Select();
            return ds.Tables[0];
        }

------------- stored proc ---------------------------------------
CREATE PROCEDURE dbo.getReturnsByLocation

(
@place_id varchar(30),
@model_id varchar(30),
@start_date datetime,
@end_date datetime,
@exclude_npf_abuse varchar(30),
@rc_ctry varchar(30),
@psCustomerIDs1 varchar(8000),
@psCustomerIDs2 varchar(8000) = NULL,
@psCustomerIDs3 varchar(8000) = NULL,
@psCustomerIDs4 varchar(8000) = NULL,
@psCustomerIDs5 varchar(8000) = NULL,
@psCustomerIDs6 varchar(8000) = NULL,
@psCustomerIDs7 varchar(8000) = NULL,
@psCustomerIDs8 varchar(8000) = NULL,
@psCustomerIDs9 varchar(8000) = NULL,
@psCustomerIDs10 varchar(8000) = NULL,
@psCustomerIDs11 varchar(8000) = NULL,
@psCustomerIDs12 varchar(8000) = NULL
)
AS

--
-- Create temp table with the list of customer IDs.
--
CREATE TABLE #TempCustomerIDs (CustomerID varchar(16))
INSERT INTO #TempCustomerIDs
    SELECT CustomerID FROM
        fnParseCustomerIDs(
            @psCustomerIDs1,
            @psCustomerIDs2,
            @psCustomerIDs3,
            @psCustomerIDs4,
            @psCustomerIDs5,
            @psCustomerIDs6,
            @psCustomerIDs7,
            @psCustomerIDs8,
            @psCustomerIDs9,
            @psCustomerIDs10,
            @psCustomerIDs11,
            @psCustomerIDs12)

        select
                count(serial_id_rcv) return_cnt, CustomerID, Regio

        from
                (select distinct notification_id, model_id, serial_id_rcv, return_reason, rpr_cntr_state, received_dt, t.CustomerID, '' as Regio
                  from Repairs r join #TempCustomerIDs t on r.customer_ship_to = t.CustomerID
                  where (received_dt between @start_date and @end_date) and REPAIR_TYPE = 'D'
                  and model_id = @model_id
                  and customer_ship_to = @place_id
                  and NPF_FLAG = 'N'
                          and ABUSE_FLAG = 'N') tbl
        group by Regio, CustomerID
        order by count(serial_id_rcv) desc


DROP TABLE #TempCustomerIDs
GO