Hi,
I need to achieve the groupby ID value using sql in outsystems. So that the output of the sql query will look like 2nd table.
Refer to the attached image.
I have a test where i have 2 entities: NameList (with the names), NameListHobbies (with the hobbies and a foreign key to Namelist)
this is the SQL
SELECT MyList.[Id], '','', STUFF(( SELECT ',' + {NameListHobbies}.[Hoby] FROM {NameListHobbies} WHERE {NameListHobbies}.[NameListId] = MyList.[Id] FOR XML PATH('')), 1, 1, '') FROM {NameList} MyList GROUP BY MyList.[Id]
That gives this output:
Not sure if this is the most efficient sql one can write, but perhaps you can tweak it.
you can do that with a custom SQL statement and the T-SQL command STRING_AGG. See an example here
SELECT
[Hobby].NameId,
[Hobby].Surname,
[Hobby].Name,
STRING_AGG([Hobby].Hobby, ';') AS Hobbies
FROM [Hobby]
GROUP BY [Hobby].NameId, [Hobby].Surname, [Hobby].Name
STRING_AGG is not working in outsystems. If it works for you can you share the oml file.
If this function does not work, read this:
https://www.outsystems.com/forums/discussion/64828/string-agg-not-work-in-sql/
Sorry. We have an on-premise installation with a SQL Server 2017, thats why it is working.
I am getting the output like this if i use the stuff() function. But i need the values to be grouped based on id
can you show the sql ?
I tried to achieve it using id and the values. But,here all the values are appearing for all ids
Need to achieve this using single entity
That would not change the SQL significantly since i do a group by on the main SELECT:
SELECT MyList.[Id], MyList.Name, MyList.SurName, STUFF(( SELECT ',' + {MyTable}.[Hoby] FROM {MyTable} WHERE ({MyTable}.[Name] = MyList.[Name] AND {MyTable}.[SurName] = MyList.[SurName])
FOR XML PATH('')), 1, 1, '') FROM {MyTable} MyList GROUP BY MyList.[Id], MyList.Name, MyList.SurName
But be aware that with this you can't identify on a key, you need to have the name and surname in your where clause.
you are using a foreign key so that you can use that in where clause. So, when we use a single entity how do we use the where clause?
WHERE ({MyTable}.[Name] = MyList.[Name] AND {MyTable}.[SurName] = MyList.[SurName])
Make it as specific as you can.
So, did you have any luck getting what you need?
If not, please show me your entity and attributes and i will write the sql for you.
Hi Siva,
If your goal is just to show it like that in your application, you can also consider using a webblock for the hobbies that gets the ID as inputparameter and show a list of hobbies for that ID. You can put that webblock in the cell for hobbies in your main recordlist.
Btw, if you can change the entity structure, then I would also recommend changing this to have an entity with persons, an entity with hobbies and an intermediate entity connecting those (in case of an n-to-n connection).
Thank you Stefano.