替换CSV字符串中的值

小编典典

替换CSV字符串中的值

sql

我有一个用逗号分隔的产品列表，并且由于该项目列表已被新产品项目替换，因此我试图用新产品项目列表来修改此CSV列表。

create table #tmp (
  id int identity(1,1) not null,
  plist varchar(max) null
)

create table #tmpprod (
  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

drop table #tmp
drop table #tmpprod

我正在尝试使用split fn转换为行，然后替换这些值，然后再次将列转换为行。还有其他可能吗？

输出为：

1 111,11,112,17,113
2 22,222,333,25
3 109,6,8,110

阅读 206

2021-04-14

共1个答案

小编典典

将您的逗号分隔列表转换为XML。使用数字表XQuery和position()获取具有它们在字符串中的位置的单独ID。使用for xml path('')带有left outer jointo#tempprod和order by的技巧构建逗号分隔的字符串position()。

;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

2021-04-14