Sql Server 同一张表，同一行数据，条件查询合并

1.Students表 

-- ----------------------------
-- Table structure for Students
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Students]') AND type IN ('U'))
    DROP TABLE [dbo].[Students]
GO

CREATE TABLE [dbo].[Students] (
  [ID] varchar(36) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [Name] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [Age] int  NULL,
  [Sex] char(1) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[Students] SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE [dbo].[Students] ADD CONSTRAINT [PK__Students__3214EC277E961692] PRIMARY KEY CLUSTERED ([ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

-- ----------------------------
-- Records of Students
-- ----------------------------
INSERT INTO [dbo].[Students] ([ID], [Name], [Age], [Sex]) VALUES (N'10001', N'小明', N'18', N'1')
GO

INSERT INTO [dbo].[Students] ([ID], [Name], [Age], [Sex]) VALUES (N'10002', N'小亮', N'19', N'1')
GO

INSERT INTO [dbo].[Students] ([ID], [Name], [Age], [Sex]) VALUES (N'10003', N'小红', N'19', N'0')
GO

INSERT INTO [dbo].[Students] ([ID], [Name], [Age], [Sex]) VALUES (N'10004', N'小清', N'20', N'0')
GO

INSERT INTO [dbo].[Students] ([ID], [Name], [Age], [Sex]) VALUES (N'10006', N'小王', N'21', N'0')
GO
 

2、查询要求，按照年龄排序，男女分组查询显示数据；

 with tb as(
select  Row_Number() over(order by Age) as Number,  Name+'  '+'男' Name1,'' Name2
from Students  where Sex = '1' 
union all 
select  Row_Number() over(order by Age) as Number,'' Name1,Name+'  '+'女' Name2
from Students   where Sex = '0'
  ) select Number,Name1=replace((select ','+Name1 from tb where Number=t.Number for XML path('')),',',''),
  Name2=replace((select ','+Name2 from tb where Number=t.Number for XML path('')),',','')
  from tb t group by Number;

查询结果：