SQL 两行两列显示一行四列或一行两列

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

前言

两行两列显示一行四列

id	A	B
1	aaa	bbb
1	ccc	ddd

==>转化为

id	A	B	A	B
1	aaa	bbb	ccc	ddd

SQL 一对多转为一对一

解决

方法一

select   id,    (select max(A) || '~' || max(B)
          from dual
         where id=1) A,
       (select min(A) || '~' || min(B)
          from dual
         where id=1) B
 from dual

显示

不想拼接的可以尝试最下边的方法

id	A	B
1	aaa~bbb	ccc~ddd

优点

写在子查询中可以动态传参

缺点

只能针对两行数据多行不行

方法二

select id,
( select ROW_NUMBER() OVER (partition BY id ORDER BY id desc,price_batch_id desc) AS rid ,d.a|| '~' ||d.b from  dual d where rownum=1),
( select ROW_NUMBER() OVER (partition BY id ORDER BY id desc,price_batch_id desc) AS rid ,d.a|| '~' ||d.b  from  dual d where rownum=2)
from  dual   
--格式化 
select (select ROW_NUMBER() OVER(partition BY id ORDER BY id desc, price_batch_id desc) AS rid,
               d.a || '~' || d.b
          from dual d
         where rownum = 1),
       (select ROW_NUMBER() OVER(partition BY id ORDER BY id desc, price_batch_id desc) AS rid,
               d.a || '~' || d.b
          from dual d
         where rownum = 2)
  from dual