How can I write SQL query to get the data in below format:

Question

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 25-Apr-21 6:24am

mohit Gupta 2021

Updated 25-Apr-21 8:57am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2021-04-25T08:57:00

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;