I have a database design that display the rooms occupied by different departments.
In the above screenshot, Room1 is occupied by Finance and HR. Current output shows the 2 rows of records for Room1 which is not expected. The output should be Room1 followed by list of department in one record.
May I know how can I achieve the above output?
I am exploring to iterate the list of departments to find out which departments belonged to the same room and adding to a list. But i'm not sure how to achieve this.
Hello XNegma,
For a performant solution, one where you do not use a block with an aggregate per row of your table, you should use an advanced SQL using STUFF and XMLPATH, see the following stack overflow example:
https://stackoverflow.com/questions/16507239/join-comma-delimited-data-column
This way you query the database only once, and not once for the table and then again for each table row.
Regards,
Daniel