Oracle sql combine two row

How can I combine two volumn in oracle sql without using CASE?
I have to combine First name and LastName in the same row.

I have a table as below:

FirstName     LastName
Ken           Chan
John          Ng
Joe           Lam

The data type of them is VARCHAR2.

I try to apply the code as follow


SELECT CONCAT(LastName, ‘,’, FirstName) as FullName from LIST order by Place;

SELECT LastName, ‘,’, FirstName as FullName from LIST order by Place;

But both of them have the same error ORA-00909:invalid number of arguments.

May I also ask how can I not adding the ‘,’ while there is missing LastName or FirstName?
Such as not adding ‘,’ when there is only having LastName Chan. The FullName only display as Chan but not ,Chan.

Thanks a lot for helping me.

>Solution :

Use Concatenation Operator that is ||. It concatenates character strings and results in another character string.
To make , comma optional in case if any of the LastName or firstName field is empty or null, use CASE statement.

Solution for your problem:

SELECT LastName || (CASE WHEN LastName IS NOT NULL AND FirstName IS NOT NULL THEN ',' END) || FirstName as FullName
FROM LIST
ORDER BY place;

Working Example: db<>fiddle Link

For more info on Concatenation Operator follow below link to official docs:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.htm

Leave a Reply