将两行合并为一,同时替换空值 [英] Merging two rows to one while replacing null values
本文介绍了将两行合并为一,同时替换空值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有以下数据库表
Name | Nickname | ID
----------------------
Joe Joey 14
Joe null 14
现在,我想执行一条select语句,将这两列合并为一,同时替换空值.结果应如下所示:
Now I want to do a select statement that merges these two columns to one while replacing the null values. The result should look like this:
Joe, Joey, 14
哪个sql语句可以管理这个问题(如果可能的话)?
Which sql statement manages this (if it's even possible)?
推荐答案
最简单的解决方案:
SQL> select * from t69
2 /
NAME NICKNAME ID
---------- ---------- ----------
Joe Joey 14
Joe 14
Michael 15
Mick 15
Mickey 15
SQL> select max(name) as name
2 , max(nickname) as nickname
3 , id
4 from t69
5 group by id
6 /
NAME NICKNAME ID
---------- ---------- ----------
Joe Joey 14
Michael Mickey 15
SQL>
如果您拥有11gR2,则可以使用新-fangled LISTAGG()函数,但否则,将上面的语句包装到SELECT中将NAME和NICKNAME列连接起来就足够简单了.
If you have 11gR2 you could use the new-fangled LISTAGG() function but otherwise it is simple enough to wrap the above statement in a SELECT which concatenates the NAME and NICKNAME columns.
这篇关于将两行合并为一,同时替换空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文