0

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']
Denis The Menace
  • 49
  • 7
  • You have to explain your question in more discrete terms. What does your input table(s) look like, what are the exact output(s) you are expecting? This will make it easy for people to read and answer. – khan Dec 05 '21 at 18:09
  • Thanks, Ill make more of an effort – Denis The Menace Dec 07 '21 at 13:13

1 Answers1

0

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
Timogavk
  • 809
  • 1
  • 7
  • 20