在存储过程中有这样的查询：
declare @col1_list varchar(max) , @col2_list varchar(max)
declare @tbl TABLE (col1 int , col2 int)
set @col1_list = '2|6|7|4|3|'
set @col2_list = '1|'
想要将数据拆分到表中，它应该像
col1 col2
2 1
6 1
7 1
4 1
3 1
例如，也许col1在列表中有一个值，而col2中有许多值
set @col1_list = '2|'
set @col2_list = '1|2|3|4'
它应该像：
col1 col2
2 1
2 2
2 3
2 4
怎么做 ？
编辑： *我没有功能的版本，使用XML *
declare @col1_list varchar(max) , @col2_list varchar(max)
declare @tbl TABLE (col1 int , col2 int)
set @col1_list = '2|6|7|4|3|'
set @col2_list = '1|'
DECLARE @myXML1 AS XML = N'<H><r>' + REPLACE(@col1_list, '|', '</r><r>') + '</r></H>'
DECLARE @myXML2 AS XML = N'<H><r>' + REPLACE(@col2_list, '|', '</r><r>') + '</r></H>';
with mycte as (SELECT Vals1.id.value('.', 'NVARCHAR(50)') AS val1
FROM @myXML1.nodes('/H/r') AS Vals1(id)),
mycte1 as (SELECT Vals2.id.value('.', 'NVARCHAR(50)') AS val2
FROM @myXML2.nodes('/H/r') AS Vals2(id))
insert into @tbl (col1,col2)
select val1,val2
from mycte,mycte1
where val1<>'' and val2<>''
select * from @tbl
试试这个：
CREATE FUNCTION [dbo].[ufnSplit] (@string NVARCHAR(MAX))
RETURNS @parsedString TABLE (rowNo INT,val NVARCHAR(MAX))
AS
BEGIN
DECLARE @separator NCHAR(1)
SET @separator='|'
DECLARE @position int
SET @position = 1
DECLARE @i INT
SET @string = @string + @separator
SET @i=1
WHILE charindex(@separator,@string,@position) <> 0
BEGIN
INSERT into @parsedString
SELECT @i, substring(@string, @position, charindex(@separator,@string,@position) - @position)
SET @position = charindex(@separator,@string,@position) + 1
SET @i=@i+1
END
RETURN
END
例如：
SELECT ISNULL(t1.val, MAX(t2.val) OVER ()), ISNULL(t2.val, MAX(t2.val) OVER () )
FROM
(SELECT * FROM [dbo].[ufnSplit]('1|2|3')) t1
FULL OUTER JOIN
(SELECT * FROM [dbo].[ufnSplit]('4')) t2
ON t1.rowNo = t2.rowNo
结果：
col1 col2
---- -----
1 4
2 4
3 4
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句