How to Concatenate Multiple Rows of Data Into Single Row

How to Concatenate Multiple Rows of Data Into Single Row

  

Hi All,


Can anyone help, please? I have just recently started learning Outsystems and can't seem to fine a video or forum to help me how to start doing this idea. The logic is very tricky for me.


1. I have this kind of data model.

2. I want a view table like this.


The [Subjects] column concatenates all the subject under that particular [Name].


Thank you and best regards,

Ean

Solution

Hello Jane,

One way to do it is to use a SQL Query to build the view you need. You could build a query with a subquery that takes the subject rows and concatenates them into a single column. Here's the query:

SELECT
    {Teachers}.[Name],
    {ClassGrade}.[Label],
    (SELECT STUFF((SELECT ', ' + Label FROM {Teacher_Subjects} INNER JOIN {Subjects} ON {Subjects}.Id = {Teacher_Subjects}.SubjectId WHERE {Teacher_Subjects}.TeacherId = {Teachers}.Id FOR XML PATH('')), 1, 1, ''))
FROM
    {Teachers}
INNER JOIN {ClassGrade}ON {Teachers}.[ClassGradeId] = {ClassGrade}.Id

Do you already know how to use SQL Queries? If you don't, here's the video that explains how to do it: http://www.outsystems.com/learn/lesson/858/data-queries/

Solution

If you don't want to use advanced queries there are at least two other options, which are a bit slower.  

You can do an aggregate with an empty column for subjects and then loop over the list and with a new aggregate to find only the subjects add those to the empty column.

Or you can create a web block with the subjects in the correct layout and add the webblock to the tablerecord.

My preferred option is the one from Aurelio, but if you don't understand that SQL, you can choose one of my suggestions.

Kind regards,

Remco

Aurelio Junior wrote:

Hello Jane,

One way to do it is to use a SQL Query to build the view you need. You could build a query with a subquery that takes the subject rows and concatenates them into a single column. Here's the query:

SELECT
    {Teachers}.[Name],
    {ClassGrade}.[Label],
    (SELECT STUFF((SELECT ', ' + Label FROM {Teacher_Subjects} INNER JOIN {Subjects} ON {Subjects}.Id = {Teacher_Subjects}.SubjectId WHERE {Teacher_Subjects}.TeacherId = {Teachers}.Id FOR XML PATH('')), 1, 1, ''))
FROM
    {Teachers}
INNER JOIN {ClassGrade}ON {Teachers}.[ClassGradeId] = {ClassGrade}.Id

Do you already know how to use SQL Queries? If you don't, here's the video that explains how to do it: http://www.outsystems.com/learn/lesson/858/data-queries/

Hi Mr. Aurelio,


Thank you so much! This is appreciated. I am now watching the link you shared.


Best regards,

Jane