select * FROM (select ID,max(CASE sex when '男' then '王小' else null end)男, max(case sex when '女' then '大小' else NULL end )女 from employee
group by ID union allselect ID,max(CASE sex when '男' then '孙小' else NULL end)男, max(case sex when '女' then '王五' else NULL end )女 from employee group by ID ) G order by id
select ID,max(CASE sex when '男' then '王小' + '孙小' else null end)男, max(case sex when '女' then '大小'+'王五' else NULL end )女 from employee
group by ID
use Tempdb go --> -->
if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([id] int,[name] nvarchar(10),[sex] nvarchar(2)) Insert #T select 1,N'王小',N'男' union all select 1,N'孙小',N'男' union ALL select 1,N'孙小2',N'女' union all select 2,N'大小',N'女' union all select 2,N'五大',N'女' GO --方法1 ;WITH Tab AS (SELECT *,Row=ROW_NUMBER()OVER(PARTITION BY sex ORDER BY ID) from #T) SELECT a.[id],[男]=a.[name],[女]=b.[name] FROM Tab AS a LEFT JOIN Tab AS b ON a.Row = b.Row AND a.sex!=b.sex WHERE a.sex = N'男' UNION ALL SELECT a.[id],[男]=b.[name],[女]=a.[name] FROM Tab AS a LEFT JOIN Tab AS b ON a.Row = b.Row AND a.sex!=b.sex WHERE a.sex = N'女' AND b.ID IS NULL/* id 男 女 1 王小 孙小2 1 孙小 大小 2 NULL 五大 */Select [id]=ROW_NUMBER()OVER(ORDER BY sex),sex,STUFF((SELECT ','+name FROM #T WHERE sex=t.sex ORDER BY id FOR XML PATH('')),1,1,'')AS Name from #T AS t GROUP BY sex /* 1 男 王小,孙小 2 女 孙小2,大小,五大 */
(select ID,max(CASE sex when '男' then '王小' else null end)男,
max(case sex when '女' then '大小' else NULL end )女
from employee
group by ID
union allselect ID,max(CASE sex when '男' then '孙小' else NULL end)男,
max(case sex when '女' then '王五' else NULL end )女
from employee
group by ID ) G order by id
max(case sex when '女' then '大小'+'王五' else NULL end )女
from employee
group by ID
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(10),[sex] nvarchar(2))
Insert #T
select 1,N'王小',N'男' union all
select 1,N'孙小',N'男' union ALL
select 1,N'孙小2',N'女' union all
select 2,N'大小',N'女' union all
select 2,N'五大',N'女'
GO
--方法1
;WITH Tab
AS
(SELECT *,Row=ROW_NUMBER()OVER(PARTITION BY sex ORDER BY ID) from #T)
SELECT a.[id],[男]=a.[name],[女]=b.[name]
FROM Tab AS a
LEFT JOIN Tab AS b ON a.Row = b.Row AND a.sex!=b.sex
WHERE a.sex = N'男'
UNION ALL
SELECT a.[id],[男]=b.[name],[女]=a.[name]
FROM Tab AS a
LEFT JOIN Tab AS b ON a.Row = b.Row AND a.sex!=b.sex
WHERE a.sex = N'女'
AND b.ID IS NULL/*
id 男 女
1 王小 孙小2
1 孙小 大小
2 NULL 五大
*/Select [id]=ROW_NUMBER()OVER(ORDER BY sex),sex,STUFF((SELECT ','+name FROM #T WHERE sex=t.sex ORDER BY id FOR XML PATH('')),1,1,'')AS Name from #T AS t GROUP BY sex
/*
1 男 王小,孙小
2 女 孙小2,大小,五大
*/