Sql Bigquery – concat two columns into a new column

I want to concat two columns shown below into one new column.

I’m using this query:

SELECT
CONCAT( A_Bill_ID , ' ', B_BILL_ID ) AS BILL_ID
FROM
`table.tmp_140222`

From this query, the result will be like this.

My problem is I want to concat only columns that don’t have a similar value so that the new column value will not duplicate two times. From the above result, the ID is duplicated since both of the columns has the same value.

Can anyone help me?

Thanks

>Solution :

You can use a case statement like this –

SELECT
case when A_Bill_ID = B_BILL_ID then a_bill_Id 
else CONCAT( A_Bill_ID , ' ', B_BILL_ID ) 
end AS BILL_ID
FROM `table.tmp_140222`