transpose bigquery records and concatenate the result as a string and and alternative array

I have the following table.

create or replace table t1.cte1 as
WITH t1 AS (
  SELECT 1 as id,'eren' AS last_name UNION ALL
  SELECT 1 as id,'yilmaz' AS last_name UNION ALL
  SELECT 1 as id,'kaya' AS last_name
)
SELECT id,ARRAY_AGG(STRUCT(last_name)) AS last_name_rec
FROM t1
GROUP BY id;

with test as (
select x.id, x.lname_agg,y.last_name  from
(
select id, STRING_AGG(h.last_name,' ') lname_agg FROM
  t1.cte1
  LEFT JOIN
  UNNEST(last_name_rec) AS h
  group by id
  ) x,
  (select id,h.last_name last_name  FROM
  t1.cte1
  LEFT JOIN
  UNNEST(last_name_rec) AS h
  group by last_name,id) y
) select id ,sp.string_flatten_dedup( lname_agg,' ') concat_last_name, last_name from test;

I get the following output. How can I make this more efficient. final output 1

The function i used is as follows.

CREATE OR REPLACE FUNCTION sp.string_flatten_dedup(string_value string, delim string) AS (

                        ARRAY_TO_STRING
                            (ARRAY(SELECT distinct string_value
                                   FROM UNNEST(SPLIT(string_value, delim)) AS string_value
                                   order by string_value desc, string_value),
                             delim)

);

I would also like to be able to store the data where concat_last_name as an array with a data structure something like below.

id, last_name,last_name_array
1, 'eren',[' eren',yilmaz','kaya']
1, 'yilmaz',[' eren',yilmaz','kaya']
1, 'kaya',[' eren',yilmaz','kaya']

Answer

Seems like you want something like this

WITH t1 AS (
  SELECT 1 as id,'eren' AS last_name UNION ALL
  SELECT 1 as id,'yilmaz' AS last_name UNION ALL
  SELECT 1 as id,'kaya' AS last_name
)
SELECT id, last_name, ARRAY_AGG(last_name) OVER (PARTITION BY id) AS last_name_rec
FROM t1

or like this if you want string value from an array

WITH t1 AS (
  SELECT 1 as id,'eren' AS last_name UNION ALL
  SELECT 1 as id,'yilmaz' AS last_name UNION ALL
  SELECT 1 as id,'kaya' AS last_name
)
SELECT id, last_name, TO_JSON_STRING(ARRAY_AGG(last_name) OVER (PARTITION BY id)) AS last_name_rec
FROM t1