时间戳

首页 > 解决方案 > 想要将行转换为列

oracle - 想要将行转换为列

问题描述

我有一张桌子如下

Customer _no    | receipt_no
----------------------------
A               | 123
A.              | 234
A.              | 345
B.              | 465
B.              | 675

我想要结果为

Customer _no | receipt_1 | receipt_2
A.           | 123.      | 234
A.           | 345.      | Null
B.           | 465.      | 675

请建议如何做到这一点

标签： oracleoracle-sqldeveloper

解决方案

如果我理解正确，您希望每个客户有两列。如果是这样，这是一种选择。

SQL> with test (cno, rno) as
  2    (select 'A', 123 from dual union all
  3     select 'A', 234 from dual union all
  4     select 'A', 345 from dual union all
  5     select 'A', 444 from dual union all
  6     select 'A', 555 from dual union all
  7     select 'B', 456 from dual union all
  8     select 'B', 675 from dual
  9    ),
 10  inter as
 11    (select cno, rno,
 12       row_number() over (partition by cno order by cno, rno) rn,
 13       round(row_number() over (partition by cno order by cno, rno) /2) grp
 14     from test
 15    )
 16  select cno,
 17    max(decode(mod(rn, 2), 1, rno)) r1,
 18    max(decode(mod(rn, 2), 0, rno)) r2
 19  from inter
 20  group by cno, grp
 21  order by cno, grp;

C         R1         R2
- ---------- ----------
A        123        234
A        345        444
A        555
B        456        675

SQL>

推荐阅读