Click here to Skip to main content
15,074,181 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Table:

Id(1,2,3,4,5)
Name('M','N','B','V','C')
Gender('M','F','M','M','F')

Result Required:

Id(1,2,3,5,4)
Gender('M','F','M','F','M')

They want one row Male gender then Female then male so on.......

What I have tried:

I thought of using concept of self join but not able to exactly figure out
Posted
Updated 25-Apr-21 8:57am
Comments
Wendelius 25-Apr-21 13:01pm
   
In order for us to help we need to know the structure of the tables.
mohit Gupta 2021 25-Apr-21 13:37pm
   
Its in question already
Wendelius 25-Apr-21 13:46pm
   
So does the table contain columns Id, Name, and Gender? How the id's would change? If I understand the description correctly, Id 4 is male in the data but in the result it's female...
mohit Gupta 2021 25-Apr-21 13:48pm
   
No its M only in result too
Wendelius 25-Apr-21 14:55pm
   
Sorry, I read it wrong.

1 solution

One way to do this is to divide the data into two portions based on gender and then utilize ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^] function.

Consider the following example
with data as (
         select 1 as Id, 'M' as Name, 'M' AS Gender
   union select 2 as Id, 'N' as Name, 'F' AS Gender
   union select 3 as Id, 'B' as Name, 'M' AS Gender
   union select 4 as Id, 'V' as Name, 'M' AS Gender
   union select 5 as Id, 'C' as Name, 'F' AS Gender
)
select id, name,  gender, ROW_NUMBER() over (order by id)
from  data
where gender = 'M'
union
select id, name, gender, ROW_NUMBER() over (order by id) + 0.5
from  data
where gender = 'F'
order by 4;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900