||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
-- Add the T-SQL statements to compute the return value here
SET @RESULT = cast(((@RowNumber-1)/@RowsInGroup) as int)+1
-- Return the result of the function
with t AS
ROW_NUMBER() over(PARTITION BY PROD_ID
order by PRPH_ID),2) as RowGrouping
INNER JOIN PRPH_ProductPhoto
ON PROD_ID = PRPH_PROD_ID
select *, ROW_NUMBER() over(PARTITION BY t.PROD_ID, RowGrouping
order by t.PROD_ID) as ColumnGroping from t
Notice that @RowNumber input is provided using Row_Number() function and by providing my partition and order by (more info available on Microsoft site here) also I have used Common Table Expression (CTE) which works the same as embeded view.
Hope that helps