34,591
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([LinearCode] nvarchar(4000))
Insert #T1
select N'1' union all
select N'1.1' union all
select N'1.2.3'
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([DataSourceId] int,[DataSourceDescription] nvarchar(34))
Insert #T2
select 1,N'城市年鉴' union all
select 2,N'二、地级以上城市统计资料' union all
select 3,N'(一)人口、劳动力及土地资源'
Go
--测试数据结束
DECLARE @count INT ,@i INT ,@sql VARCHAR(500)
SELECT @count= COUNT(1) FROM #T2
SET @i=1
if not object_id(N'Tempdb..#tab1') is null
drop table #tab1
Create table #tab1(
[LinearCode] nvarchar(25),
[LinearCode_.] VARCHAR(max)
)
--把#t1的数据插入#tab1
INSERT INTO #tab1
SELECT [LinearCode],[LinearCode]+'.' AS [LinearCode_.]
FROM #t1
--把#t2的数据插入#tab2
if not object_id(N'Tempdb..#tab2') is null
DROP TABLE #tab2
SELECT * ,row_number() over( ORDER BY [DataSourceId]) AS num
into #tab2 FROM #t2
--循环替换
WHILE @i<=@count
BEGIN
SET @sql='update #tab1 set [LinearCode_.]=replace([LinearCode_.],cast([DataSourceId] as varchar(5))+''.'',[DataSourceDescription]+''>'')
FROM #tab1 ,#tab2
WHERE #tab2.num='+cast(@i AS VARCHAR(5))
SET @i=@i+1
PRINT @sql
EXEC (@sql)
END
UPDATE #tab1 SET [LinearCode_.]=left([LinearCode_.],LEN([LinearCode_.])-1)
SELECT * FROM #tab1
LinearCode LinearCode_.
------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 城市年鉴
1.1 城市年鉴>城市年鉴
1.2.3 城市年鉴>二、地级以上城市统计资料>(一)人口、劳动力及土地资源
(3 行受影响)
CREATE FUNCTION dbo.f_splite
(
@s VARCHAR(8000) , --待分拆的字符串
@split VARCHAR(10) --数据分隔符
)
RETURNS @re TABLE (id INT,col VARCHAR(100) )
AS
BEGIN
DECLARE @splitlen INT
DECLARE @i INT = 1
SET @splitlen = LEN(@split + 'a') - 2
WHILE CHARINDEX(@split, @s) > 0
BEGIN
INSERT @re
VALUES ( @i,LEFT(@s, CHARINDEX(@split, @s) - 1) )
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
SET @i = @i +1
END
INSERT @re
VALUES ( @i,@s )
RETURN
END
GO
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([LinearCode] nvarchar(25))
Insert #T1
select N'1' union all
select N'1.1' union all
select N'1.2.3'
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([DataSourceId] int,[DataSourceDescription] nvarchar(34))
Insert #T2
select 1,N'城市年鉴' union all
select 2,N'二、地级以上城市统计资料' union all
select 3,N'(一)人口、劳动力及土地资源'
Go
--测试数据结束
SELECT * ,
STUFF(( SELECT '>' + #T2.DataSourceDescription
FROM ( SELECT col
FROM dbo.f_splite(LinearCode, '.')
) t1
JOIN #T2 ON t1.col = #T2.DataSourceId
FOR
XML PATH('')
), 1, 1, '') AS DataSourceDescription
FROM #T1