To return distinct value

To return distinct value

  

Hi,

I have the following table below.

LessonName
LessonDate
LessonTime
TrainerName
Zumba
22/1/2018
0930am
Ms Karen Lo
Zumba
23/1/2018
1030am
Mr Joshua Tan
Yoga
22/2/2018
0830am
Ms Jane Tan


I want to have a distinct lesson name instead of duplicate lesson name due to the different trainer for different lesson date as shown below. LessonDate and Lesson Time is part of the filter search field thus i cannot omit the lesson date and lesson time columns. I intend to use aggregate for this but trying to figure out how to achieve the below table. Appreciate if anyone can help?

LessonName
TrainerName
Zumba
Ms Karen Lo, Mr Joshua Tan
Yoga
Ms Jane Tan


P.S. Please pardon the title of the question as i not sure what to name it as.

That was quite a transformation, I believe you cannot use simple aggregate to achieve this.

What i will do:

1. create a structure (name it LessonAndTrainer) with attribut like: LessonName, TrainerName

2. create a variable (name it LessonAndTrainer) with type of above structure (LessonAndTrainer)

3. create a variable (name it LessonAndTrainers  *plural form*) with type of: List of LessonAndTrainer (can contain multiple records)

4. create aggregate to get distinct LessonName

5. loop DistinctLessonName aggregate, for each record:
    a. set LessonAndTrainer.LessonName = LessonName
    b. set LessonAndTrainer.TrainerName = "" (empty string)
    c. create aggregate to get TrainerName for that LessonName
    d. loop TrainerName aggregate above, for each record:
         - set LessonAndTrainer.TrainerName = LessonAndTrainer.TrainerName + ", " + TrainerName
    e. set LessonAndTrainer.TrainerName = substr(LessonAndTrainer.TrainerName, 2, length(LessonAndTrainer.TrainerName)  // this is done to remove the first comma (", ")
    f. use ListAppend action to append LessonAndTrainer record to LessonAndTrainers list.

6. Use LessonAndTrainers List in your TableView of ListView instead of Aggregate.

Harlin Setiadarma wrote:

That was quite a transformation, I believe you cannot use simple aggregate to achieve this.

What i will do:

1. create a structure (name it LessonAndTrainer) with attribut like: LessonName, TrainerName

2. create a variable (name it LessonAndTrainer) with type of above structure (LessonAndTrainer)

3. create a variable (name it LessonAndTrainers  *plural form*) with type of: List of LessonAndTrainer (can contain multiple records)

4. create aggregate to get distinct LessonName

5. loop DistinctLessonName aggregate, for each record:
    a. set LessonAndTrainer.LessonName = LessonName
    b. set LessonAndTrainer.TrainerName = "" (empty string)
    c. create aggregate to get TrainerName for that LessonName
    d. loop TrainerName aggregate above, for each record:
         - set LessonAndTrainer.TrainerName = LessonAndTrainer.TrainerName + ", " + TrainerName
    e. set LessonAndTrainer.TrainerName = substr(LessonAndTrainer.TrainerName, 2, length(LessonAndTrainer.TrainerName)  // this is done to remove the first comma (", ")
    f. use ListAppend action to append LessonAndTrainer record to LessonAndTrainers list.

6. Use LessonAndTrainers List in your TableView of ListView instead of Aggregate.

Hi,

Thanks for the recommendation, just want to check if you have any sample to illustrate the above method?


Hey jace jace,


Imho harlin provided you with much information.

I suggest to start implementing that on your own.

That way you will learn it the right way.

If you get stuck on the way, provide us your module so we can point you in the right direction.

Good luck!

Hello Jace,

There are other 3 ways of doing what you want, 1 of them using a single aggregate, 1 using one aggregate and a web block with a list (the most obvious but not very "efficient", with a variation that solves this), and 1 using 1 advanced query. All of them without resorting to logic.

I'll prepare an article explaining these approaches and an example. 

Studying other codes is also important to learn and create patterns that will allows us to be "creative" and find good solutions for our problems. :)

Cheers

Eduardo Jauch

P.S. In any case, I recommend following J. advice and really implement Harlin solution, as it gives you an excellent opportunity to practice many different concepts.

Cheers,

Eduardo Jauch

Jace Jace wrote:

Hi,

I have the following table below.

LessonName
LessonDate
LessonTime
TrainerName
Zumba
22/1/2018
0930am
Ms Karen Lo
Zumba
23/1/2018
1030am
Mr Joshua Tan
Yoga
22/2/2018
0830am
Ms Jane Tan


I want to have a distinct lesson name instead of duplicate lesson name due to the different trainer for different lesson date as shown below. LessonDate and Lesson Time is part of the filter search field thus i cannot omit the lesson date and lesson time columns. I intend to use aggregate for this but trying to figure out how to achieve the below table. Appreciate if anyone can help?

LessonName
TrainerName
Zumba
Ms Karen Lo, Mr Joshua Tan
Yoga
Ms Jane Tan


P.S. Please pardon the title of the question as i not sure what to name it as.

hi Jace-jace,

The title should be "

ListAGG in SQLSERVER

" :)


Cookbook:

Just to complement if it is an SQL Advance - SQL Server database:

- Test Result:


regards,

bb