title: output semicolon separated values in field in databricks SQL
body: <p>Desired outcome:</p>
  <pre><code>+---------+-----------------------------+
  | ID PR   | Related Repeating Event(s)  |
  +---------+-----------------------------+
  | 1658503 | 1615764;1639329             |
  +---------+-----------------------------+  
  </code></pre>
  <p>Is there a way to write a query in sql / databricks without using a User-defined aggregate functions (UDAFs)? I've tried concat(), GROUP_CONCAT(), LISTAGG but none of these work or are not supported in databricks (&quot;This function is neither a registered temporary function nor a permanent function registered in the database 'default'.&quot;.</p>
  <p>I found this User-defined aggregate functions (UDAFs) description in the databricks documentation but don't know how to implement it (<a href="https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-functions-udf-aggregate.html#user-defined-aggregate-functions-udafs&amp;language-sql" rel="nofollow noreferrer">https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-functions-udf-aggregate.html#user-defined-aggregate-functions-udafs&amp;language-sql</a>)</p>
  <p>Would anybody have a hint for me or a link?</p>
  <p>What I have is this basic query:</p>
  <pre><code>%sql
  SELECT
    pr_id,
    data_field_nm,
    field_value
  FROM
    gms_us_mart.txn_pr_addtl_data_detail_trkw_glbl --(18)
  WHERE
   pr_id = 1658503
   AND data_field_nm = 'Related Repeating Deviation(s)'
  </code></pre>
  <p>Which gives as output:</p>
  <pre><code>+---------+--------------------------------+-------------+
  | pr_id   | data_field_nm                  | field_value |
  +---------+--------------------------------+-------------+
  | 1658503 | Related Repeating Deviation(s) | 1615764     |
  | 1658503 | Related Repeating Deviation(s) | 1639329     |
  +---------+--------------------------------+-------------+
  </code></pre>
  <p>Correct answer is (thanks to @Alex Ott):</p>
  <pre><code>%sql
  SELECT
   pr_id AS IDPR,
    concat_ws(';', collect_list(field_value)) AS RelatedRepeatingDeviations
  FROM
    gms_us_mart.txn_pr_addtl_data_detail_trkw_glbl
  WHERE
    data_field_nm = 'Related Repeating Deviation(s)'
    AND pr_id = 1658503
  GROUP BY
    pr_id,
    data_field_nm;
  </code></pre>
  <p>Gives desired outcome:</p>
  <pre><code>+---------+-----------------------------+
  | IDPR    | RelatedRepeatingDeviations  |
  +---------+-----------------------------+
  | 1658503 | 1615764;1639329             |
  +---------+-----------------------------+  
  </code></pre>