#custom group

New Post has been published on http://www.varindersandhu.in/2011/10/25/sql-server-user-defined-group/

SQL Server - User Defined Group

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

Example:

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

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

User Defined Group

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

User Defined Group

Using Crystal Report

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

User Defined Group

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