如何转动在TSQL第二列

问题描述：

rname ename 
Advises Grad_student 
Advises Faculty 
Chairs Department 
Chairs Faculty

我试图进入它的格式如下：

rname ename1  ename2 
advises grad_student faculty 
chairs department faculty

这里是我试过到目前为止：

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

这里就是我恢复：

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

我该如何修复我的代码才能获得1行？

答

您可以使用row_number()与conditional aggregation：

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

好吧，这解决了它，也没有CTE见过。谢谢！ – user2146212

@ user2146212 - np，ctes是'common-table-expressions' - 有点像临时视图。解决方案的关键是使用'row_number（）'建立一个分组。 – sgeddes

答

如果只有过二，然后就去做这样。简短而甜美。

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

答

在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))

如何转动在TSQL第二列

相关推荐