locked
Grouping for a One To Many relationship RRS feed

  • Question

  • Hello,

    I have a link table called 'Subspecialties Link' which contains PostNumber and SpecialtyID. PostNumber is the Primary Key to the 'Posts' table and SpecialtyID is the Primary Key to the specialties table. The relationship is a One to Many relationship so that a Post can have many Subspecialties related to it.

    In my report, I wish to display the subspecialties  along with many other fields. My problem is that if a Post has, for example, 3 subspecialties then the Post is displayed 3 times in the report. The only field in these rows that is different is the subspecialty. Is there any way to change this so that the Post is only displayed once regardless of the number of subspecialties it has? Ideally, I would be able to concatenate the subspecialties into the one cell delimited by ' ; '

    Thanks,
    John
    Tuesday, February 5, 2008 4:18 PM

Answers

  • Hi,

     

    If your database server is SQL Server then one way to do this would be to create a scalar function in the database which uses a cursor to loop through the subspecialties for a postnumber and return the concatenated string.  You could call the function from a stored procedure which returns the data from the posts table and the concatenated string returned by the function call.

     

    Hope this helps,

    Bill

     

    Tuesday, February 5, 2008 4:41 PM