Big query - Concatenate strings horizontally

Question

I have a data with a column for the first name and a column for the last name. I try to combine the them into one column via code:

SELECT GROUP_CONCAT_UNQUOTED(full_name,' ') 
from (Select first_name as check from [DATA]), 
     (select last_name  as check from [DATA])

But it returns a one row string with

Anna Alex Emma Sean .... Miller Smith White ...

but what I wanted was actually a column like

Anna Miller
Alex Smith
Emma White
...

Can you please tell me what I should do differently? Thanks!

Answer 1

You need to use CONCAT and the trim functions

SELECT CONCAT(rtrim(ltrim(first_name)),' ',rtrim(ltrim(last_name))) AS full_name
FROM
  (SELECT 'Anna' AS first_name,
          ' Miller ' AS last_name),

Answer 2

You can also use the double pipe || concatenation operator for concatenation of strings (see here for more info):

select 
    trim(first_name) || ' ' || trim(last_name)
from
    ...