Generate Report with Alternate Order of an Attribute (say Gender) in SQL Server

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.

Advertisement