SQL Server – User Defined Group

I had requirement to make the User Defined Group in Reports (i.e. Crystal Reports)

Example:

CREATE TABLE test_custom_group
(
id INT,
dept VARCHAR(20),
detail VARCHAR(30),
salary NUMERIC(5,2)
)

Created a Function

CREATE FUNCTION test_fn
(
)
RETURNS @temp_table TABLE
(
    sno int,
    dept varchar(20),
    grp varchar(10)
)

AS

BEGIN

	INSERT @temp_table(sno,dept,grp )
	SELECT 1,'C','A'
	UNION all
	SELECT 2,'A','A'
	UNION all
	SELECT 3,'D','B'
	UNION all
	SELECT 4,'E','B'
	UNION all
	SELECT 5,'B','B'

  RETURN
END

Created a Store Procedure

USE test_db 

IF EXISTS (SELECT 1 FROM sysobjects WHERE name='sp_ test_custom_group'
and TYPE='P')

DROP PROCEDURE sp_ test_custom_group
GO

CREATE PROCEDURE sp_ test_custom_group

WITH ENCRYPTION
AS
BEGIN

SELECT
	ISNULL(aa.sno,99) as SNo
	,so.dept
	,so.detail
	,so.salary
	,ISNULL(aa.grp,'C') AS GRP
  FROM test_custom_group so
  LEFT JOIN
	(
	Select * from test_fn()
	) aa
  ON aa.dept = so.dept
ORDER BY AA.sno 

END

Using Crystal Report

Now  simply we can group the records on the custom column as GRP

If you have any other way, you can share as comment.

Thanks to my colleague Bhavna Kakkar who documented this post for me.