如何在tsql中转动第二列

Here's my current dataset:

这是我目前的数据集:

rname   ename
Advises Grad_student
Advises Faculty
Chairs  Department
Chairs  Faculty

I'm trying to get it into this format:

我想把它变成这种格式:

rname    ename1        ename2
advises  grad_student  faculty
chairs   department    faculty

Here's what i've tried so far:

这是我到目前为止所尝试的:

select distinct 
r1.rname, r1.ENAME as ename1,r2.ENAME as ename2
from [dbo].[RELATIONSHIPS] r1
inner join(
select distinct 
RNAME, ENAME

from [dbo].[RELATIONSHIPS]) r2
on r1.RNAME = r2.RNAME
where r1.ENAME <> r2.ENAME

order by r1.rname

Here's what i'm getting back:

这是我要回来的:

rname   ename1        ename2
Advises Grad_student  Faculty
Advises Faculty       Grad_student
Chairs  Department    Faculty
Chairs  Faculty       Department

How would I would I fix my code in order to get only 1 row back?

我怎么能修复我的代码才能只获得一行?

3 个解决方案

#1

You can use row_number() with conditional aggregation:

您可以将row_number()与条件聚合一起使用:

with cte as  (
  select rname, ename, row_number() over (partition by rname order by ename) rn
  from relationships
  )
select rname, 
  max(case when rn = 1 then ename end) ename1,
  max(case when rn = 2 then ename end) ename2
from cte
group by rname

SQL Fiddle Demo

SQL小提琴演示

#2

In oracle

 select * from 
 (select rname,ename,
 RANK() over (partition by rname order by ename) Id
 FROM relationships)
 PIVOT(MAX(ename) for Id in (1 as ename1, 2 as ename2))

#3

If there are only ever two, then just do it this way. Short and sweet.

如果只有两个,那就这样做吧。简短又甜蜜。

SELECT  rname,
        MIN(ename) ename1,
        MAX(ename) ename2
FROM relationships
GROUP BY rname

#1