||Occasionally you might have 'creative' requirements (we like them) and may require you to divide your query results into groups with equal number of rows.
||1) Let say you are asked to produce a report which shows top 5 products for each category or one to show top five clients for each region.
2) Maybe you need to do 'paging' on your report (like Google)
3) What about reports which display multiple pictures for each product? You might want to display them in 3 columns and unknown number of rows to save space and make it more user friendly.
||Our function udfRowNumberGrouping takes two parameters:
1) @RowNumber - this is special parameter which is calculated using of the T-SQL Ranking function and in the sample codes below we will show you have to use it.
2) @RowsInGroup - In here you specify how manu rows you want in the group.
Below is the function code and example how we use it.
-- Author: Emil Glownia
-- Website: http://www.itcouple.co.uk
-- Create date: July-2010
-- Description: This function is used to divide query result
-- into groups with specified number of rows.
CREATE FUNCTION [dbo].[udfRowNumberGrouping]
-- Add the parameters for the function here
@RowNumber AS INT,
@RowsInGroup AS INT
-- Declare the return variable here
DECLARE @RESULT AS INT
To get full access to this page
Become a member or Sign in
Membership is just $3.99/month