GROUP Data and Building a string

• Digs

  SSCertifiable

  Points: 7333

  More actions

  March 23, 2005 at 3:11 pm

  #163962

  Hi, SQL 2000

  I have data like this

  SKU, CUSTOMER, SOURCE

  125,NULL,'A'

  125,NULL,'B'

  126,NULL,'C'

  NULL,C345,'C'

  NULL,C355,'B'

  NULL,C355,'A'

  I would like it to look like this

  125,NULL,'A,B'

  126,NULL,'C'

  NULL,C355,'A,B'

  NULL,C345,'C'

  The 'A,B' is a build in the same field, just grouping the date up that is the same sku or same customer number, and building a string for the SOURCE reference..

  Any ideas for this newbie ??

• wz700

  SSCertifiable

  Points: 7133

  More actions

  March 23, 2005 at 10:45 pm

  #548650

  scalar function can help.

  create function fn_groupbystr(sku int, customer varchar(5))

  returns varchar(10)

  as

  begin

    declare @ren varchar(10)

    select @ren = case when @ren is null then '' else @ren + ',' end + SOURCE from tablename

     where (sku is null or  sku = @sku) and (customer is null or customer = @customer)

    return @ren

  end

  select sku, customer, dbo.fn_groupbystr(sku,customer) as source from tablename

• Digs

  SSCertifiable

  Points: 7333

  More actions

  March 26, 2005 at 12:47 pm

  #549047

  AweSome...dude thanks heaps