Aggregate text column?

Paul shared this question 15 months 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

@Flappy Bird Do you have any ideas?

Leave a Comment
 
Attach a file