如何将单个列值拆分为两个不带空值的列

Question

下面是原始表格的样子。

| Catid| Name  |
| ---- | ----  |
|1     | ramesh|
|1     | suresh|
|1     | mahesh|
|2     | rahul |
|2     | rajesh|

我需要0/p作为

|catname1 | catname2|
| ----    | ----    |
|ramesh   | rahul   |
|suresh   | rajesh  |
|mahesh   |         |

我尝试了下面的查询，但这产生空值

select (case when s1.catid=1 then s1.name end) as cat1name,
       (case when s1.catid=2 then s1.name end) as cat2name
from deemucty.sample12 as s1 

Answer 1

由于您没有提供按名称排序的东西，所以我按字母顺序排序。

select      min (case catid when 1 then name end)  as catname1
           ,min (case catid when 2 then name end)  as catname2
from       (select      row_number () over (partition by catid order by name)  as rn
                       ,catid
                       ,name
            from        deemucty.sample12
            ) t
group by    rn
order by    rn
;

Answer 2

您可以通过在一个catid中的所有名称与另一个catid中的所有名称之间执行完整的外部连接来实现这一点:

select t1.name as catname1, 
       t2.name as catname2
from (
  select name, row_number() over (order by name) as rn
  from the_table
  where catid = 1
) t1
  full join (
    select name, row_number() over (order by name) as rn
    from the_table
    where catid = 2
  ) t2 on t1.rn = t2.rn

示例:http://rextester.com/KJLFAR74695