Need help regarding displaying data

• raghava_tg

  Ten Centuries

  Points: 1014

  More actions

  May 15, 2012 at 11:21 am

  #261350

  create table #tmp(name varchar(10),Gender char(1))

  insert into #tmp (name,Gender) values('A','M')

  insert into #tmp (name,Gender) values('B','M')

  insert into #tmp (name,Gender) values('C','M')

  insert into #tmp (name,Gender) values('D','M')

  insert into #tmp (name,Gender) values('F','F')

  insert into #tmp (name,Gender) values('G','F')

  insert into #tmp (name,Gender) values('H','F')

  SELECT * FROM #tmp

  nameGender

  AM

  BM

  CM

  DM

  FF

  GF

  HF

  All I need the data like in the alternative on Gender basis.

  Sample of display is as follows.

  nameGender

  AM

  FF

  BM

  GF

  CM

  HF

  DM

  Please let me know possible ways to do it.

• Daxesh Patel

  Hall of Fame

  Points: 3208

  More actions

  May 15, 2012 at 11:51 am

  #1487905

  select Name,Gender

  from (

  SELECT row_number() over(order by Name) ID,name,Gender FROM #tmp where Gender='M'

  union all

  SELECT row_number() over(order by Name)+0.5 ID,name,Gender FROM #tmp where Gender='F'

  ) a

  order by ID

• raghava_tg

  Ten Centuries

  Points: 1014

  More actions

  May 15, 2012 at 11:59 am

  #1487918

  Thanks

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 15, 2012 at 12:12 pm

  #1487931

  Something like this?

  select name, Gender

  from

  (

  select name, Gender, ROW_NUMBER() over (order by name) as RowNum

  from #tmp

  where Gender = 'M'

  union all

  select name, Gender, ROW_NUMBER() over (order by name) as RowNum

  from #tmp

  where Gender = 'F'

  ) x

  order by RowNum, Gender desc

  --EDIT: looks like I got distracted while posting and didn't refresh. The previous solution is almost the same thing I posted.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• drew.allen

  SSC Guru

  Points: 76999

  More actions

  May 15, 2012 at 3:15 pm

  #1488050

  I think that this performs better, because it only requires one scan of the table instead of two.

  SELECT * FROM #tmp

  ORDER BY ROW_NUMBER() OVER( PARTITION BY Gender ORDER BY Name ), Gender DESC

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• raghava_tg

  Ten Centuries

  Points: 1014

  More actions

  May 16, 2012 at 6:11 am

  #1488319

  Thanks to all.