Group by and what else... ?

• dobrzak-134971

  Right there with Babe

  Points: 737

  More actions

  April 5, 2006 at 8:02 am

  #167673

  Hi,

  how can I make this query (I've tried with group by)

  I have table:

  Typ Value Date

  1     11     1/1/2001

  1     12     1/2/2001

  1     16     1/6/2001

  2      2      2/3/2001

  2      4      2/14/2001

  And I would like to recive such table:

  Typ Value

  1     1/1/2001~11~1/2/2001~12~1/6/2001~16

  2      2/3/2001~2~2/14/2001~4

  Does exist any simple solution ?

  Regards,

  dobrzak

• AMIT GUPTA-263376

  SSCarpal Tunnel

  Points: 4244

  More actions

  April 5, 2006 at 8:47 am

  #631016

  Hi,

  Use this Query & Function to proceed :

  Create  function fn_Grouped(@id int,@sep varchar(1)) returns varchar(200)

  as

  Begin

  Declare @STR varchar(200)

  select @STR=coalesce(@str,'') + convert(varchar,dt,113) + @sep + convert(varchar,bk_int) +

  @sep + convert(varchar,cid) + @sep from tbl_grp where cid=@id

  if right(@str,1)=@sep

  begin

  set @STR=substring(@str,1,len(@str)-1)

  end

  return @STR

  end

   

  and then use the following Query :

  select  cid,dbo.fn_Grouped(cid,'~') from tbl_grp

  group by cid

   Thanks ,

  Regards

  Amit Gupta