Home > T - SQL > ROLLUP with CURSOR for grouping

ROLLUP with CURSOR for grouping

January 3rd, 2010 Arun Mallick Leave a comment Go to comments


Problem: I have a table for customer information with Customer Ids and Customer Type. The customer may fall into different types i.e. the customer may belong to Type A or Type B or may belong to both the groups. The requirement is to find out the count of the customers as per the type in each group and if the customer falls under more than one group, it must not be counted under the individual group rather it should be counted under the group with both the types.

Solution:

Let’s have a table named [dbo].[CustomerInfo] with the columns as CustomerID and CustomerType.

Table: dbo.CustomerInfo

Column_name Type
CustomerID int
CustomerType varchar

Below is the script for creating and populating data into it.

For creating table:

CREATE TABLE [dbo].[CustomerInfo](
	[CustomerID] [INT] NULL,
	[CustomerType] [VARCHAR](50) NULL
) ON [PRIMARY]

For populating data:

DECLARE @seed INT
DECLARE @type INT
DECLARE @customer INT
SET @seed = 10
SET @type = 65
SET @customer = 1
 
 
WHILE @type < 70
BEGIN
	WHILE @customer < @seed
	BEGIN
		INSERT INTO [dbo].[CustomerInfo]
           ([CustomerID]
           ,[CustomerType])
		SELECT REPLICATE(CAST(@customer AS NVARCHAR(2)),2), REPLICATE(CHAR(@type),2)
 
		SET @customer = @customer + 1
	END 
 
	SET @customer = 1
	SET @seed = @seed - 2
	SET @type = @type + 1
END

The data will be as below:

CustomerID CustomerType
11 AA
22 AA
33 AA
44 AA
55 AA
55 BB
55 CC
66 AA
66 BB
11 BB
22 BB
33 BB
44 BB
77 AA
77 BB
88 AA
11 CC
22 CC
33 CC
44 CC
99 AA
11 DD
22 DD
33 DD
11 EE

The requirement is to show the data in each grouping of CustomerType i.e. total number of customers in {AA,BB,CC,DD,EE},{AA,BB,CC,DD},{AA,BB,CC},…etc. The condition is that the customer must not fall under more than one group at same time.


Below is the code to get the above requirement.

 
DECLARE @Temp_Customer TABLE (
	CustomerID [VARCHAR](50) NULL,
	CustomerType [VARCHAR](100) NULL
 
) 
 
DECLARE @CurrentCustomer VARCHAR(100)
DECLARE @CurrentType     VARCHAR(100)
 
SET @CurrentCustomer = '$@'
 
DECLARE cubCur    CURSOR FOR  
 
SELECT
CASE
      WHEN GROUPING(CustomerType) = 1 THEN NULL
      ELSE CustomerType 
      END AS CustomerType,
CASE
      WHEN GROUPING(CustomerID) = 1 THEN NULL
      ELSE CustomerID 
      END AS CustomerID
FROM  dbo.CustomerInfo
GROUP BY  ROLLUP(CustomerID,CustomerType)
--GROUP BY  CustomerID,CustomerType  WITH ROLLUP /*For SQL Server 2005 and below versions */
HAVING CustomerType IS not NULL and CustomerID IS not null
ORDER BY CustomerID
 
 
 
DECLARE @Customer VARCHAR(50)
DECLARE @CType VARCHAR(50)
 
OPEN cubCur
FETCH NEXT FROM cubCur 
INTO @CType, @Customer
 
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @CurrentCustomer = '$@'
      BEGIN
            SET @CurrentCustomer = @Customer
            SET @CurrentType = @CType
      END
      ELSE IF @CurrentCustomer = @Customer 
      BEGIN
            SET @CurrentType = @CurrentType + ',' + @CType
 
      END
      ELSE
      BEGIN
        INSERT INTO @Temp_Customer
           (CustomerID
           ,CustomerType)
        SELECT @CurrentCustomer, @CurrentType
 
            SET @CurrentCustomer = @Customer
            SET @CurrentType = @CType
 
      END
 
 
 
FETCH NEXT FROM cubCur 
INTO @CType, @Customer
 
END
 
IF @CurrentCustomer <> '$@'
BEGIN
      INSERT INTO @Temp_Customer
           (CustomerID
           ,CustomerType)
        SELECT @CurrentCustomer, @CurrentType
END
 
CLOSE cubCur
DEALLOCATE cubCur
 
/*To get count of all the customers falling under each group*/
SELECT CustomerType, COUNT(*) CustomerCount FROM @Temp_Customer GROUP BY CustomerType
 
 
/*To get all the customers ids falling under each group*/
DECLARE @Temp_Type TABLE (
	CustomerID [VARCHAR](50) NULL,
	CustomerType [VARCHAR](100) NULL)
 
DECLARE CustCursor CURSOR FOR
SELECT CustomerType,CustomerID FROM @Temp_Customer 
SET @CurrentType = '$@'
OPEN CustCursor
FETCH NEXT FROM CustCursor 
INTO @CType, @Customer
 
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @CurrentType = '$@'
      BEGIN
            SET @CurrentCustomer = @Customer
            SET @CurrentType = @CType
      END
      ELSE IF @CurrentType = @CType 
      BEGIN
            SET @CurrentCustomer = @CurrentCustomer + ',' + @Customer
 
      END
      ELSE
      BEGIN
        INSERT INTO @Temp_Type
           (CustomerID
           ,CustomerType)
        SELECT @CurrentCustomer, @CurrentType
 
            SET @CurrentCustomer = @Customer
            SET @CurrentType = @CType
 
      END
 
FETCH NEXT FROM CustCursor 
INTO @CType, @Customer
 
END
CLOSE CustCursor
DEALLOCATE CustCursor
 
IF @CurrentType <> '$@'
BEGIN
      INSERT INTO @Temp_Type
           (CustomerID
           ,CustomerType)
        SELECT @CurrentCustomer, @CurrentType
END
 
SELECT CustomerType,CustomerID FROM @Temp_Type

Below is the output for both count of customers and list of customers falling under same group:

CustomerType CustomerCount
AA

2
AA,BB

2
AA,BB,CC

2
AA,BB,CC,DD

2
AA,BB,CC,DD,EE

1


CustomerType CustomerID
AA,BB,CC,DD,EE 11
AA,BB,CC,DD 22,33
AA,BB,CC 44,55
AA,BB 66,77
AA 88,99

The above code is fully working under SQL Server 2008, 2005 and 2000 versions.

Hope these queries will be helpful in pulling down your stress level.

Categories: T - SQL
  1. No comments yet.
  1. No trackbacks yet.