reading and replacing values

• zubairm76

  SSC Journeyman

  Points: 90

  More actions

  February 20, 2009 at 12:32 am

  #128524

  Hi,

  I have the following columns:

  IDWBSdroppedParentid

  111

  21.11

  31.1.11.1

  41.1.21.1

  51.1.31.1

  61.1.41.1

  71.21

  81.2.11.2

  I need a query that will read my dropped column and display results as follows in the Parentid column:

  IDWBSdroppedParentid

  1111

  21.111

  31.1.11.12

  41.1.21.12

  51.1.31.12

  61.1.41.12

  71.211

  81.2.11.27

  That is where the dropped = wbs then parentid = id of that wbs.

  Thanks

• Leo Leong

  SSCommitted

  Points: 1615

  More actions

  February 20, 2009 at 3:25 am

  #947390

  create table #tmp (id int, wbs varchar(16), dropped varchar(16), parentid int)

  insert into #tmp

  select '1','1','1', null

  union

  select '2','1.1','1', null

  union

  select '3','1.1.1','1.1', null

  union

  select '4','1.1.2','1.1', null

  union

  select '5','1.1.3','1.1', null

  union

  select '6','1.1.4','1.1', null

  union

  select '7','1.2','1', null

  union

  select '8','1.2.1','1.2', null

  select * from #tmp

  select b.id, b.wbs, b.dropped, a.id

  from #tmp a, #tmp b

  where a.wbs = b.dropped