Help please???

• vu_tran764

  Valued Member

  Points: 68

  More actions

  June 28, 2012 at 3:58 pm

  #263507

  Here is the scenario:

  I need to update GroupName field with whoever in the NAME field and in the same GROUP field. Here is what I did so far and not sure what is wrong. Can some one help me please? Thank you every much!!

  You can copy and paste it into MSSMS and work with it.

  -----------------------------------------------------------------------------------------

  -----------------------------------------------------------------------------------------

  --Create temp table and add values to each column

  CREATE TABLE #Temp(

  ID int,

  GroupID varchar(30),

  Name varchar(30),

  GroupName nvarchar (50)

  )

  INSERT INTO #Temp (ID, GroupID, Name, GroupName) values

  (1,'ABC','VU',''),(2,'ABC','MIKE',''),(3,'DCF','ERIC',''),(4,'DCF','JASON',''),(5,'DCF','LEVON','')

  GO

  ;

  --------------------------------------------------------------------

  --Check all values

  SELECT *

  FROM #Temp

  ;

  --Results

  IDGroupIDNameGroupName

  1ABCVUNULL

  2ABCMIKENULL

  3DCFERICNULL

  4DCFJASONNULL

  5DCFLEVONNULL

  --------------------------------------------------------------------

  --------------------------------------------------------------------

  --How do I want to make it look like this?

  IDGroupIDNameGroupName

  1ABCVUVU

  2ABCMIKEVU;MIKE

  3DCFERICERIC

  4DCFJASONERIC;JASON

  5DCFLEVONERIC;JASON;LEVON

  --------------------------------------------------------------------

  --------------------------------------------------------------------

  --Here is what I did:

  --Declare variables

  DECLARE @id int,@GroupID varchar(30),@Name varchar(30),@GroupName nvarchar (50), @x int

  set nocount on

  --set @GroupID

  set @x = 1

  BEGIN TRAN

  -- declare the cursor

  DECLARE Document CURSOR DYNAMIC

  FOR

  SELECT ID

  FROM #Temp

  Open Document

  FETCH NEXT FROM Document INTO @id

  WHILE @@Fetch_Status = 0

  BEGIN

  -- This is where you perform your detailed row-by-row

  -- processing.

  -- Get the next row.

  set @GroupName = @GroupName

  --set @newdocid = @prefix + cast(replace(str(@x, @padlevel),' ','0')as varchar(50))

  Update #Temp set GroupName = @GroupName + name where id=@id

  set @x = @x + 1

  FETCH NEXT FROM Document INTO @id

  END

  CLOSE Document

  DEALLOCATE Document

  COMMIT TRAN

  return

  err_handler:

  ROLLBACK TRAN

  return

  set nocount off

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  SET ANSI_NULLS ON

  GO

  ------------------------------------------------------

  --How do I want to make it look like this?

  IDGroupIDNameGroupName

  1ABCVUVU

  2ABCMIKEVU;MIKE

  3DCFERICVU;MIKE;ERIC

  4DCFJASONVU;MIKE;ERIC;JASON

  5DCFLEVONVU;MIKE;ERIC;JASON;LEVON