create table #tb(f1 varchar(100))
insert into #tb
select '5,8,9,10,13' union all
select '5,8,10,12,13'union all
select '5,9,10,12,13'union all
select '6,7,8,9,11' union all
select '1,3,4,5,11' union all
select '2,4,5,6,7' union all
select '1,3,5,7,8'
给定一个字符串如下
A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S
要求按照#tb表中f1字段的值对字符串相应位置取值,各个值以空格分开
比如 5 对于字符串9
输出结果到表:
9 Y - X 0
9 Y X + 0
9 - X + 0
2 M Y - .
A D 5 9 .
A D 9 M Y
最好用函数实现
返回表变量 ,谢谢
insert into #tb
select '5,8,9,10,13' union all
select '5,8,10,12,13'union all
select '5,9,10,12,13'union all
select '6,7,8,9,11' union all
select '1,3,4,5,11' union all
select '2,4,5,6,7' union all
select '1,3,5,7,8'
给定一个字符串如下
A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S
要求按照#tb表中f1字段的值对字符串相应位置取值,各个值以空格分开
比如 5 对于字符串9
输出结果到表:
9 Y - X 0
9 Y X + 0
9 - X + 0
2 M Y - .
A D 5 9 .
A D 9 M Y
最好用函数实现
返回表变量 ,谢谢
对应关系
比如'5,8,9,10,13'
取字符串A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S
第5 8 9 10 13个位置的值
结果9 Y - X 0
insert into #tb
select '5,8,9,10,13' union all
select '5,8,10,12,13'union all
select '5,9,10,12,13'union all
select '6,7,8,9,11' union all
select '1,3,4,5,11' union all
select '2,4,5,6,7' union all
select '1,3,5,7,8' DECLARE @str VARCHAR(50);
SET @str='A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S'--生成一个唯一标识的列
SELECT ID=IDENTITY(INT,1,1),* INTO # FROM #tb;WITH Liang AS
(
SELECT
A.ID,
A.f1,
B.x.value('.','INT') AS number,
B.x.value('for $i in .
return count($i/../*[.<<$i])+1','INT') AS Seq,
c.str
FROM (
SELECT
ID,
CONVERT(XML,'<v>'+REPLACE(f1,',','</v><v>')+'</v>') AS f1
FROM #
) AS A
CROSS APPLY A.f1.nodes('//v') AS B(x)
CROSS APPLY (
SELECT str=(
SELECT M.x.value('.','VARCHAR(20)') AS s
FROM (
SELECT
CONVERT(XML,'<v>'+REPLACE(@str,',','</v><v>')+'</v>') AS s
) AS T
CROSS APPLY s.nodes('//v') AS M(x)
WHERE M.x.value('for $i in .
return count($i/../*[.<<$i])+1','INT')=B.x.value('.','INT')
)
) AS C
)
SELECT
A.f1,
B.data
FROM (
SELECT *
FROM #
) AS A
CROSS APPLY (
SELECT data=(
SELECT
str AS [data()]
FROM Liang
WHERE ID=A.ID
ORDER BY Seq
FOR XML PATH(''),TYPE
)
) AS B
--删除测试DROP TABLE #,#tb
f1 data
----------------- -------------------
5,8,9,10,13 9 Y - X 0
5,8,10,12,13 9 Y X + 0
5,9,10,12,13 9 - X + 0
6,7,8,9,11 2 M Y - .
1,3,4,5,11 A D 5 9 .
2,4,5,6,7 N 5 9 2 M
1,3,5,7,8 A D 9 M Y(7 行受影响)
*/
sql2005不熟悉,看不懂