Question 222103 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Convert ARRAY<STRING> to STRING in HIVE

I have collect_set(label) values and wanted to convert it to string

SELECT users, collect_set(label) as pets from users_table group by users;

so that ["dog", "cat", "lion"] would become ["dog", "cat", "lion"] but in STRING format

Tags:

2

Answers


  1. 0

    Try this,

    SELECT
    users,
    concat_ws(', ', collect_set(label)) AS pets_string
FROM
    users_table
GROUP BY
    users;

    concat_ws function takes multiple arguments and concatenates them into a single string

    Login or Signup to reply.
  2. 0

    It looks like you want to convert the collect_set(label) values into a string format within a SQL query. Depending on the database system you are using, the exact function and syntax might differ. However, here’s a general approach that should work for many relational databases:

        SELECT 
    users, 
    CONCAT('[', 
           GROUP_CONCAT(DISTINCT label ORDER BY label ASC SEPARATOR ', '), 
           ']') AS pets 
FROM users_table 
GROUP BY users;

    In this query, we are using the GROUP_CONCAT function to concatenate the distinct label values for each user, ordering them alphabetically. The result will be a comma-separated string enclosed within square brackets.

    Please note that:

    1. The GROUP_CONCAT function might be named differently depending on
      your database system (e.g., STRING_AGG in PostgreSQL).
    2. The SEPARATOR argument in GROUP_CONCAT specifies the delimiter
      between concatenated values. Make sure it’s set to a comma followed
      by a space if you want a clear separation between values.
    3. This query assumes that your database supports these functions and
      that you’re using a recent version. If you encounter any issues,
      refer to your database’s documentation for appropriate functions to
      achieve this behavior.
    4. The ordering might differ slightly based on the specifics of your
      database, so be sure to adjust it accordingly.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search