On my ASP.NET webpage, I bind to a data source that selects from table1.
This results in one query that pulls all the rows. However, I would like to include as a column to display a list of todo actions for each person. In the above example, there would be 2 items for john and 2 for jane.
If I make these separate data sources, then it is another extra query for EACH person in table1, which is really inefficient on large datasets. I could make the query if they click on a person for more details, but I need it where it is all pre-populated and shown in the same table on the first load. Are there queries or design methods that handle this problem? I'm still pretty new to databases.
With SQL Server 2005+, you can use Common Table Expressions (CTEs) to create comma delimited strings in a single recursive query.
Prior to version 2005, this could be done using a user-defined function.
If it's not practically useful, then it's practically useless.