34,592
社区成员
发帖
与我相关
我的任务
分享
use tempdb
go
if object_ID('[fn_Split]') is not null
Drop function fn_Split
Go
create function fn_Split
(
@str nvarchar(max),
@split nvarchar(50)
)
returns @t table(value nvarchar(50))
as
begin
set @str=@str+@split
while(1=1)
begin
if isnull(@str,'')='' break
insert into @t(value)values(substring(@str,1,charindex(@split,@str)-1))
set @str=stuff(@str,1,charindex(@split,@str),'')
end
return
end
go
------------------------------
if not object_ID('Tempdb..#1') is null drop table #1
Go
Create table #1([Mobile] int,[Q1] nvarchar(5))
Insert #1
select 1351234567,'A-B-C' union all
select 1313245678,'A' union all
select 1331245678,'B-C' union all
select 1331245679,'B-C-D'
Go
if not object_ID('Tempdb..#2') is null drop table #2
Go
Create table #2([Q1] nvarchar(1),[Name] nvarchar(2))
Insert #2
select 'A',N'微信' union all
select 'B',N'短信' union all
select 'C',N'电话' union all
select 'D',N'邮件'
Go
----------------------------
with cte_t1 as
(
select a.Mobile,c.Name as Q1
from #1 a
cross apply dbo.fn_Split(a.Q1,'-') as b
inner join #2 c on c.Q1=b.value
)
select distinct a.Mobile,b.Q1
from cte_t1 a
cross apply(select stuff((select '-'+x.Q1 from cte_t1 x where x.Mobile=a.Mobile for xml path('')),1,1,'') as Q1
) b
/*
Mobile Q1
1313245678 微信
1331245678 短信-电话
1331245679 短信-电话-邮件
1351234567 微信-短信-电话
*/
CREATE TABLE #T(Mobile nvarchar(30),Q1 nvarchar(30))
insert into #T(Mobile,Q1) values('1351234567','A-B-C')
insert into #T(Mobile,Q1) values('1313245678','A')
insert into #T(Mobile,Q1) values('1331245678','B-C')
insert into #T(Mobile,Q1) values('1331245679','B-C-D')
select Mobile,replace(replace(replace(replace(Q1,'A','微信'),'B','短信'),'C','电话'),'D','邮件') from #T
drop table #T
select Mobile,replace(replace(replace(Q1,'A','微信'),'B','短信'),'C','邮件') from T