updating a table based on another table with concatenated results

• SQLTestUser

  Hall of Fame

  Points: 3997

  More actions

  April 22, 2011 at 2:52 pm

  #389359

  [X]

  [ID] [int] NOT NULL,

  [Type] [nvarchar]

  [Plannned] [nvarchar]

  [Y]

  [ID] [int] NOT NULL,

  [Type] [nvarchar](5)

  [Description] [nvarchar]

  Contents in Y are as

  ID Type Description

  1 H For 1

  1 H For 2

  1 H For 3

  1 HFor 4

  Now, I want the contens in y to be updated in X as

  ID Type Planned

  1 H For 1, For 2, For 3, For 4

  Deos anyone know how i could accomplish this

  I can create a new column in Y with combined results of same ID and then update X but i donot know if there is another way of doing this

  Thanks

• bhupendra.patel

  Mr or Mrs. 500

  Points: 508

  More actions

  April 22, 2011 at 11:34 pm

  #1315088

  Try this

  insert into @Table1(id,type,planned)

  SELECT p1.id,p1.type,

  ( SELECT description + ','

  FROM @Table2 p2

  WHERE p2.id = p1.id and p2.type=p1.type

  ORDER BY description

  FOR XML PATH('') ) AS planned

  FROM @Table2 p1

  GROUP BY id,type ;