title: transpose bigquery records and concatenate the result as a string and and alternative array
body: <p>I have the following table.</p>
  <pre><code>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;
  </code></pre>
  <p>I get the following output. How can I make this more efficient.
  <a href="https://i.stack.imgur.com/Jmkok.png" rel="nofollow noreferrer">final output 1</a></p>
  <p>The function i used is as follows.</p>
  <p>CREATE OR REPLACE FUNCTION
  sp.string_flatten_dedup(string_value string,
  delim string) AS
  (</p>
  <pre><code>                        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)
  </code></pre>
  <p>);</p>
  <p>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.</p>
  <pre><code>id, last_name,last_name_array
  1, 'eren',[' eren',yilmaz','kaya']
  1, 'yilmaz',[' eren',yilmaz','kaya']
  1, 'kaya',[' eren',yilmaz','kaya']
  </code></pre>