Generate Report with Alternate Order of an Attribute (say Gender) in SQL Server
Posted by Prashant on June 23, 2010
Some times we may need to generate a report which display employees/customers in alternate order of any particular attribute.This post describes for employees with alternate order of Gender i.e. first a Male employee then a Female employee and so on or vice versa.
In SQL Server this is just a simpler way. There could be may ways to do this. Here is my set based approach with ROW_NUMBER() function.
DECLARE @EMPLOYEE TABLE ( NAME VARCHAR(100) NOT NULL, GENDER VARCHAR(1) NOT NULL ) --Populate sample data INSERT INTO @EMPLOYEE(NAME,GENDER) VALUES ('Anup','M'), ('Sheetal','F') ,('Sonam','F'), ('Payal','F') ,('Parul','F'), ('Peter','M') ,('Rahul','M'), ('Roxy','F') ,('Preeti','F'), ('Manav','M') SELECT * FROM @EMPLOYEE --Result ;WITH CTE(SEQ,ENAME,GENDER) AS ( SELECT ROW_NUMBER() OVER(PARTITION BY GENDER ORDER BY GENDER), NAME, GENDER FROM @EMPLOYEE ) SELECT ENAME,GENDER FROM CTE ORDER BY SEQ, GENDER DESC GO
Also put your valuable suggestions on this.