list parent records with children in one field ?

• Charlottecb

  Hall of Fame

  Points: 3932

  More actions

  May 13, 2004 at 2:22 am

  #111234

  Hi folks.

  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, John, Jane

  2             Activities       Malcolm

  3             Training         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

• wz700

  SSCertifiable

  Points: 7133

  More actions

  May 13, 2004 at 3:06 am

  #506257

  One of ways to do it:

  create scale function (fn_list(budgetid)) list parent, chidren in csv format by passing budgetid.

  select budgetid, budgetdesc, fn_list(budgetid) as budgeholdernames from

  table.

• Charlottecb

  Hall of Fame

  Points: 3932

  More actions

  May 13, 2004 at 5:03 am

  #506271

  Thanks for the advice.

  What is a scale function, and what do I code to create it ?

  CCB.

• Wayne Lawton

  Hall of Fame

  Points: 3532

  More actions

  May 13, 2004 at 8:52 am

  #506332

  Here is an alternate way to achieve the goal.  I am sure it can be reduced to a smaller chunk of code, but this will get you started.

  DECLARE @tmpTable TABLE (GID NUMERIC IDENTITY (1,1)  NOT NULL,

           BudgetID numeric,

             budgetdesc varchar(64),

           budgetholders varchar(1024))

  declare @MinGID numeric, @MaxGID numeric, @thisgid numeric

  DECLARE @BUDGETHOLDERLIST VARCHAR(1000)

  INSERT INTO @tmpTable (BudgetID, budgetdesc) SELECT DISTINCT BUDGETID, BUDGETDESC FROM BUDGET

  select @MinGID = min (gid), @MaxGID = max(GID) from @tmpTable

  WHILE @MinGID <= @MaxGID

  begin

     set @BUDGETHOLDERLIST = NULL

     select @thisgid = gid from @tmpTable WHERE gid = @MinGID

   SELECT @BUDGETHOLDERLIST = COALESCE(@BUDGETHOLDERLIST + ',', '') + RTRIM(budgetholdername)--  as aName

       from budgetholders

     where BUDGETID = @thisgid

     update @tmpTable set budgetholders = @BUDGETHOLDERLIST WHERE gid = @thisgid

     set @mingid = @mingid + 1

  end

  select * from @tmpTable

• wz700

  SSCertifiable

  Points: 7133

  More actions

  May 14, 2004 at 1:54 am

  #506430

  My mistake, it is scalar function.