Post reply

List Parent Records with related children in one field

  • September 9, 2004 at 1:35 am

    #60423

     Hi All.

    I posted something similar a while back but never got a solution.

    SQLserver 2000.

    Can someone help with the following: -

    I have a budget table (parent) and a budget holders table (child)

    I would like to return the following

    BudgetID   BudgetDesc    BudgetHolderNames

    1             Transport       Fred, Jane, John

    2             Activities       Malcolm

    3             Training         Alan, Fred Sam, Susan

     

    What I am trying to do is list the child records horizontally amonst the parent records. the two tables are linked on budgetID.

    Thanks for any assistance.

    CCB

  • September 9, 2004 at 8:29 am

    #522327

    I strongly suggest you to leave that to the Presentation (Report or Client app) side. But just for the fun of it here is an example on how to do it on the server

     

    create table budget (BudgetID int ,  BudgetDesc varchar(50))

    create table BudgetHolder (BudgetID int , HolderName varchar(50))

    insert into budget (BudgetID,BudgetDesc) values (1, 'Transport')

    insert into budget (BudgetID,BudgetDesc) values (2, 'Activities')

    insert into budget (BudgetID,BudgetDesc) values (3, 'Training')

    insert into BudgetHolder (BudgetID, HolderName) values(1,'Fred')

    insert into BudgetHolder (BudgetID, HolderName) values(1,'Jane')

    insert into BudgetHolder (BudgetID, HolderName) values(1,'John')

    insert into BudgetHolder (BudgetID, HolderName) values(2,'Malcolm')

    insert into BudgetHolder (BudgetID, HolderName) values(3,'Alan')

    insert into BudgetHolder (BudgetID, HolderName) values(3,'Fred')

    insert into BudgetHolder (BudgetID, HolderName) values(3,'Sam')

    insert into BudgetHolder (BudgetID, HolderName) values(3,'Susan')

    create function dbo.udf_ListOfHolders (@BudgetId int)

    returns varchar(8000)

    as

    begin

     Declare @retval varchar(8000)

     select @retval = Isnull(@RetVal +', ','') +  HolderName from BudgetHolder where BudgetId = @BudgetId

     return @retval

    end

      

    select BudgetId, BudgetDesc, dbo.udf_ListOfHolders(BudgetId) as HolderNames

    from budget  

     

    HTH 

     


    * Noel

  • September 9, 2004 at 9:26 am

    #522347

    Perfect.

    Thank you Noel.

    CCB

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply