Combine strings in a certain order

Question

Sample table

NUMBER	DEAL_NUMBER	NAME1	NAME2
1	T01	TOM	JERRY
2	T02	LEBRON	STEVEN

Would like output as below:

NUMBER	DEAL_NUMBER	NAME1	NAME2	Name_COM
1	T01	TOM	JERRY	LEBRON TOM STEVEN JERRY
2	T02	LEBRON	STEVEN	LEBRON TOM STEVEN JERRY

By using string_agg function can solve with it, but it is not convenient enough.

CREATE TEMP FUNCTION EXCHANGE_PLACE(STR STRING)
RETURNS STRING
AS

((
          SELECT ARRAY_TO_STRING(array_reverse(ARRAY_LIST),' ') FROM (SELECT SPLIT(STR,' ')ARRAY_LIST)
));

WITH TBL_D_CUSTOMER AS
(
 SELECT "1" AS NUMBER,"T01" AS DEAL_NUMBER,"TOM" AS NAME1, "JERRY" AS NAME2 UNION ALL 
 SELECT "2","T01","LEBRON","STEVEN"
)


SELECT 
       *,
EXCHANGE_PLACE(STRING_AGG(NAME1,' ')OVER(PARTITION BY DEAL_NUMBER)) || ' ' || EXCHANGE_PLACE(STRING_AGG(NAME2,' ')OVER(PARTITION BY DEAL_NUMBER)) AS NAME_COM
FROM TBL_D_CUSTOMER

Is there any approach can do it better?

Answer 1

Consider below approach

select *, array_to_string(
  array_reverse(array_agg(NAME1) over win) || array_reverse(array_agg(NAME2) over win)
  , ' ') as Name_COM
from TBL_D_CUSTOMER 
window win as (partition by DEAL_NUMBER)      

if applied to sample data in your question - output is