Click here to Skip to main content
13,708,425 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi

I have a scenario and struggling to get an output in the desired format. I am out of ideas on how to achieve it!

I have an employee table with name and gender as columns in it. The sample data looks like this:

Name   Gender
-------------
abc     M
xyz     M
mno     F
pqr     F
tuv     M


I want output to be like as follows:(each row followed by opposite gender)

Name    Gender
---------------
abc     M
mno     F
xyz     M
pqr     F
tuv     M


What I have tried:

i tried using correlated subquery, but couldn't achieve what i wanted.
Posted 17-Feb-18 9:07am
Updated 17-Feb-18 11:49am
v2
Comments
PIEBALDconsult 17-Feb-18 17:09pm
   
Interesting, I have an idea...
I will say that I can't guarantee the order, but the M-F-M... order should be doable.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

This shows how ROW_NUMBER can be used to get the M-F-M ordering, but also (I think) demonstrates that the rows must also be ordered by Name (because ROW_MUMBER requires an ORDER BY).
And it may not work well with other datasets either.

SELECT *
INTO #temp
FROM
(
  SELECT 'abc' [Name] , 'M' [Gender]
UNION ALL
  SELECT 'xyz' [Name] , 'M' [Gender]
UNION ALL
  SELECT 'mno' [Name] , 'F' [Gender]
UNION ALL
  SELECT 'pqr' [Name] , 'F' [Gender]
UNION ALL
  SELECT 'tuv' [Name] , 'M' [Gender]
) T

SELECT [Name]
, [Gender]
FROM
(
  SELECT * 
  , ROW_NUMBER() OVER ( PARTITION BY [Gender] ORDER BY [Name] )  [rn]
  FROM #temp
) T
ORDER BY [rn] , [Gender] DESC


Maybe this will provide some insight.
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180920.1 | Last Updated 17 Feb 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100