Aggregate text column?

Paul shared this question 2 years ago
Completed

Hello!

I am trying to build a report that shows our users and a list of their permissions.

On our DB, we have a unique record for each user permission.

So the report would look something like:


User1 | Incident Management

User1 | Change Management

User1 | Asset Management

User2 | Incident Management

User2 | Change Management


When I would like it to look like...


User1 | Incident Management; Change Management; Asset Management

User2 | Incident Management; Change Management


I found this post that seemed to be what I needed:

Aggregate a text column | Community (yellowfinbi.com)


I made a function in custom-functions.xml but it doesn't work and I am wondering if I have used the wrong syntax (we're using SQLServer while the example in the link above uses MySQL).


  <function>
    <name>Group Concat, SQLServer</name>
    <argument>
      <index>1</index>
      <name>String</name>
      <datatype>text</datatype>
    </argument>
    <sql>
      <![CDATA[
        STRING_AGG($1) 
      ]]>
    </sql>
<aggregate>$1</aggregate>
    <database>SQLServer</database> 
    <return>text</return>
  </function>

Any help with working this out would be much appreciate, thanks for your time! :)

Replies (2)

photo
1

(Apologies! This question can be closed. Upon looking further, it's not actually SQL being used despite the error saying 552 SQL error. The correct one for what I wanted was LISTAGG.)

photo
1

This is exactly what I was looking for! Being able to aggregate text fields (like concatenating comments or notes) would be super helpful for reporting crazy flips 3d. Hope this feature gets more attention—thanks for bringing it up!

photo
1

With its perfect mix of chaos and control, Geometry Arrow offers more than just gameplay—it offers a challenge.

photo
Leave a Comment
 
Attach a file