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

前言

两行两列显示一行四列

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 

显示

id	A	B
1	aaa~bbb	ccc~ddd

优点

可以获得多行多列 调整Rownum即可

缺点

不可以动态传参（建议可以创建虚表，关联rid形式传参）