如何在SQL Server中获取所需的输出 [英] How to get the desired output in SQL server

问题描述

嗨



我有一个场景，并努力获得所需格式的输出。我对如何实现它没有想法！



我有一个员工表，其中包含名称和性别作为列。示例数据如下所示：

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

我尝试了什么：



i尝试使用相关子查询，但无法达到我想要的效果。

What I have tried:

i tried using correlated subquery, but couldn't achieve what i wanted.

推荐答案

这显示如何使用ROW_NUMBER获取MFM排序，但（我认为）也证明了行也必须按名称排序（因为ROW_MUMBER需要ORDER BY）。

它可能不适用于其他数据集。

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.

这篇关于如何在SQL Server中获取所需的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！