如何使用 SQL Server 数据库中的函数/存储过程实现以下目的

Question

>我有下表，数据如下

Tab1
FutureMISBoundaryVersion  CurrentMISBoundaryVersion  FutureHAMBoundaryVersion  CurrentHAMBoundaryVersion 
2:21,5:50,4:55,7:80,9:33  2:12,5:40,4:35,7:60,9:87   2:52,5:90,4:75,7:30,9:57  2:42,5:60,4:95,7:70,9:37   

必须将此键值对拆分为，并且必须按以下方式将每个键的值插入到另一个表中

FutureMIS-OAKVersion |FutureMIS-HAMVersion |FutureMIS-DURVersion | FutureMIS-BURVersion| FutureMIS-YRTVersion |DeviceMIS-OAKVersion|DeviceMIS-HAMVersion |DeviceMIS-DURVersion| DeviceMIS-BURVersion| DeviceMIS-YRTVersion
33     |              80     |            21       |            55       |  50                  | 87                 |  60                 |12                  |35                   | 40

i，e：当它在tab1中找到"FutureMISBoundaryVersion"列时，其值 '2：21,5：50,4：55,7：80,9：33'将被拆分，其值的插入方式是，键2的相应值i，e：21将入到FutureMIS-DURVersion列中。

同样，键 5 的值 50 将入到 FutureMIS-BURVersion 列中，依此类推，对于其他键，以此类推

当它找到"CurrentMISBoundaryVersion"列时，"2：12,5：40,4：35,7：60,9：87"将被拆分，其值以这样的方式插入，即键2的相应值i，e：12将入到CurrentMIS-DURVersion列中，同样键5的40值将入到DeviceMIS-YRTVersion列中，
依此类推源表的其他列。

表结构可能会扩展，因为我只显示了 4 个源表列，但所有列的逻辑保持不变

Answer 1

老实说，非常时髦的要求。 请注意，下面的解决方案仅适用于SQL Server 2016 +，因为我使用JSON来解析数据。但是，您可以编写自己的解析器，在这种情况下，代码几乎适用于所有版本的SQL Server。

解析函数：

CREATE FUNCTION dbo.ParseIt(@Type NVARCHAR(255),@Value NVARCHAR(MAX))
RETURNS @Parsed TABLE (Code NVARCHAR(255),Value NVARCHAR(255))
AS
BEGIN
INSERT INTO @Parsed(Code,Value)
SELECT @Type + '-' + m.Code + 'Version' AS [Code],p.[1] AS [Value]
FROM (
SELECT j.[key] AS [ID],i.[key],i.value
FROM OPENJSON('["' + REPLACE(@Value,',','","') + '"]') j
CROSS APPLY OPENJSON('[' + REPLACE(j.[value],':',',') + ']') i
) a
PIVOT(MAX(a.value) FOR a.[key] IN ([0],[1])) p
INNER JOIN ( VALUES
(2,'DUR')
,(4,'BUR')
,(5,'YRT')
,(7,'HAM')
,(9,'OAK')
) m(ID, Code) ON m.ID = p.[0]
;
RETURN;
END

初始数据：

DECLARE @Table TABLE (FutureMISBoundaryVersion NVARCHAR(MAX), CurrentMISBoundaryVersion NVARCHAR(MAX),FutureHAMBoundaryVersion NVARCHAR(MAX),CurrentHAMBoundaryVersion NVARCHAR(MAX));
INSERT INTO @Table(FutureMISBoundaryVersion,CurrentMISBoundaryVersion,FutureHAMBoundaryVersion,CurrentHAMBoundaryVersion)VALUES
('2:21,5:50,4:55,7:80,9:33','2:12,5:40,4:35,7:60,9:87','2:52,5:90,4:75,7:30,9:57','2:42,5:60,4:95,7:70,9:37')
;

代码：

SELECT COALESCE(p.[FutureMIS-OAKVersion],'') AS [FutureMIS-OAKVersion]
,COALESCE(p.[FutureMIS-HAMVersion],'') AS [FutureMIS-HAMVersion]
,COALESCE(p.[FutureMIS-DURVersion],'') AS [FutureMIS-DURVersion]
,COALESCE(p.[FutureMIS-BURVersion],'') AS [FutureMIS-BURVersion]
,COALESCE(p.[FutureMIS-YRTVersion],'') AS [FutureMIS-YRTVersion]
,COALESCE(p.[DeviceMIS-OAKVersion],'') AS [DeviceMIS-OAKVersion]
,COALESCE(p.[DeviceMIS-HAMVersion],'') AS [DeviceMIS-HAMVersion]
,COALESCE(p.[DeviceMIS-DURVersion],'') AS [DeviceMIS-DURVersion]
,COALESCE(p.[DeviceMIS-BURVersion],'') AS [DeviceMIS-BURVersion]
,COALESCE(p.[DeviceMIS-YRTVersion],'') AS [DeviceMIS-YRTVersion]
FROM (
SELECT f.Code,f.Value FROM @Table t CROSS APPLY dbo.ParseIt('FutureMIS',t.FutureMISBoundaryVersion) f
UNION ALL 
SELECT f.Code,f.Value FROM @Table t CROSS APPLY dbo.ParseIt('DeviceMIS',t.CurrentMISBoundaryVersion) f
) a
PIVOT(MAX(a.Value) FOR a.Code IN ([DeviceMIS-BURVersion],[DeviceMIS-DURVersion],[DeviceMIS-HAMVersion],[DeviceMIS-OAKVersion]
,[DeviceMIS-YRTVersion],[FutureMIS-BURVersion],[FutureMIS-DURVersion],[FutureMIS-HAMVersion],[FutureMIS-OAKVersion]
,[FutureMIS-YRTVersion])) p
;

Answer 2

以下查询会将逗号分隔的字符串2:21,5:50,4:55,7:80,9:33解析为单个组件2:21、5:30等。从那里您可以使用类似的方法从aa:bb中提取bb。

由于键值对的格式为aa:bb，因此您可以使用datepart(hour, 'aa:bb')和datepart(minute, 'aa:bb')来提取aa和bb

; with 
Tab1 as
(
select  val  = '2:21,5:50,4:55,7:80,9:33'
)
select  t.*, k1.k, k2.k, k3.k, k4.k, k5.k
from    Tab1 t
cross apply
(
select  i = charindex(',', t.val),
k = substring(t.val, 1, charindex(',', t.val + ',', 1) - 1)
) k1
cross apply
(
select  i = charindex(',', t.val, k1.i + 1),
k = substring(t.val, k1.i + 1, charindex(',', t.val + ',', k1.i + 1) - k1.i - 1)
) k2
cross apply
(
select  i = charindex(',', t.val, k2.i + 1),
k = substring(t.val, k2.i + 1, charindex(',', t.val + ',', k2.i + 1) - k2.i - 1)
) k3
cross apply
(
select  i = charindex(',', t.val, k3.i + 1),
k = substring(t.val, k3.i + 1, charindex(',', t.val + ',', k3.i + 1) - k3.i - 1)
) k4
cross apply
(
select  i = charindex(',', t.val, k4.i + 1),
k = substring(t.val, k4.i + 1, charindex(',', t.val + ',', k4.i + 1) - k4.i - 1)
) k5

Answer 3

这在SQL Server中是一个痛苦。 您可以使用递归 CTE 执行此操作：

with cte as (
select convert(varchar(max), left(FutureMISBoundaryVersion, charindex(',', FutureMISBoundaryVersion) - 1)) as FutureMISBoundaryVersion,
convert(varchar(max), left(CurrentMISBoundaryVersion, charindex(',', CurrentMISBoundaryVersion) - 1)) as CurrentMISBoundaryVersion,
convert(varchar(max), left(FutureHAMBoundaryVersion, charindex(',', FutureHAMBoundaryVersion) - 1)) as FutureHAMBoundaryVersion,
convert(varchar(max), left(CurrentHAMBoundaryVersion, charindex(',', FutureMISBoundaryVersion) - 1)) as CurrentHAMBoundaryVersion,
stuff(FutureMISBoundaryVersion, 1, charindex(',', FutureMISBoundaryVersion), '') + ',' as FutureMISBoundaryVersion_rest,
stuff(CurrentMISBoundaryVersion, 1, charindex(',', CurrentMISBoundaryVersion), '') + ',' as CurrentMISBoundaryVersion_rest,
stuff(FutureHAMBoundaryVersion, 1, charindex(',', FutureHAMBoundaryVersion), '') + ',' as FutureHAMBoundaryVersion_rest,
stuff(CurrentHAMBoundaryVersion, 1, charindex(',', CurrentHAMBoundaryVersion), '') + ',' as CurrentHAMBoundaryVersion_rest,
1 as lev
from t
union all
select convert(varchar(max), left(FutureMISBoundaryVersion_rest, charindex(',', FutureMISBoundaryVersion_rest) - 1)) as FutureMISBoundaryVersion,
convert(varchar(max), left(CurrentMISBoundaryVersion_rest, charindex(',', CurrentMISBoundaryVersion_rest) - 1)) as CurrentMISBoundaryVersion,
convert(varchar(max), left(FutureHAMBoundaryVersion_rest, charindex(',', FutureHAMBoundaryVersion_rest) - 1)) as FutureHAMBoundaryVersion,
convert(varchar(max), left(CurrentHAMBoundaryVersion_rest, charindex(',', CurrentHAMBoundaryVersion_rest) - 1)) as CurrentHAMBoundaryVersion,
stuff(FutureMISBoundaryVersion_rest, 1, charindex(',', FutureMISBoundaryVersion_rest), '') as FutureMISBoundaryVersion_rest,
stuff(CurrentMISBoundaryVersion_rest, 1, charindex(',', CurrentMISBoundaryVersion_rest), '') as CurrentMISBoundaryVersion_rest,
stuff(FutureHAMBoundaryVersion_rest, 1, charindex(',', FutureHAMBoundaryVersion_rest), '') as FutureHAMBoundaryVersion_rest,
stuff(CurrentHAMBoundaryVersion_rest, 1, charindex(',', CurrentHAMBoundaryVersion_rest), '') as CurrentHAMBoundaryVersion_rest,
lev + 1
from cte
where FutureMISBoundaryVersion_rest like '%,%'
)
select FutureMISBoundaryVersion, CurrentMISBoundaryVersion, FutureHAMBoundaryVersion, CurrentHAMBoundaryVersion, lev
from cte;

这是一个数据库<>小提琴。