0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for power users of Apple hardware and software

declare @tmp table
(
  id int identity(1,1) not null,
  plist varchar(max) null
)

declare @tmpprod table
(
  oldid int null,
  newid int null
)

insert into @tmp
select '10,11,15,17,19'
union
select '22,34,44,25'
union
select '5,6,8,9'

insert into @tmpprod
select 5, 109
union
select 9, 110
union
select 10, 111
union
select 15, 112
union
select 19, 113
union
select 30, 114
union
select 34, 222
union
select 44, 333

;with C as
(
  select T.id,
         N.number as Pos,
         X.PList.value('(/i[position()=sql:column("N.Number")])[1]', 'int') as PID
  from @tmp as T
    cross apply (select cast('<i>'+replace(plist, ',', '</i><i>')+'</i>' as xml)) as X(PList)
    inner join master..spt_values as N
      on N.number between 1 and X.PList.value('count(/i)', 'int')
  where N.type = 'P'  
)
select C1.id,
       stuff((select ','+cast(coalesce(T.newid, C2.PID) as varchar(10))
              from C as C2
                left outer join @tmpprod as T
                  on C2.PID = T.oldid
              where C1.id = C2.id
              order by C2.Pos
              for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '')
              
from C as C1
group by C1.id

fork query

created mar 6 12 Mikael Eriksson