字符串通过分隔符转换为表字符串为 1|1.1|2.2|3.3|4.4|5.5|6.2,2|1.2|2.2|3.2|4.5|5.7|6.3,3|1.5|2.6|3.5|
字符串通过分隔符转换为表
字符串为 1|1.1|2.2|3.3|4.4|5.5|6.2,2|1.2|2.2|3.2|4.5|5.7|6.3,3|1.5|2.6|3.5|4.4|5.9|6.5,4|1.5|2|3.5|4|5|6.9
转换为
id value1 value2 value3 value4 value5 value6
1 1.1 2.2 3.3 4.4 5.5 6.2
2 1.2 2.2 3.2 4.5 5.7 6.3
3 1.5 2.6 3.5 4.4 5.9 6.5
4 1.5 2 3.5 4 5 6.9 高手 字符串拆分
[解决办法]
这个题目用 C# 似乎更容易吧。
[解决办法]
WITH a1 (msg) AS
(
SELECT '1
[解决办法]
1.1
[解决办法]
2.2
[解决办法]
3.3
[解决办法]
4.4
[解决办法]
5.5
[解决办法]
6.2,2
[解决办法]
1.2
[解决办法]
2.2
[解决办法]
3.2
[解决办法]
4.5
[解决办法]
5.7
[解决办法]
6.3,3
[解决办法]
1.5
[解决办法]
2.6
[解决办法]
3.5
[解决办法]
4.4
[解决办法]
5.9
[解决办法]
6.5,4
[解决办法]
1.5
[解决办法]
2
[解决办法]
3.5
[解决办法]
4
[解决办法]
5
[解决办法]
6.9'
)
,a2 AS
(
SELECT b.msg,ROW_NUMBER() OVER (ORDER BY GETDATE()) re
FROM
(SELECT msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),',','</v><v>')+'</v></root>') FROM a1) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
,a3 AS
(
SELECT a.re,b.msg,ROW_NUMBER() OVER (PARTITION BY a.re ORDER BY GETDATE()) re2
FROM
(SELECT re,msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),'
[解决办法]
','</v><v>')+'</v></root>') FROM a2) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
SELECT [1] id,[2] value1 ,[3] value2,[4] value3,[5] value4,[6] value5,[7] value6
FROM a3
PIVOT (MAX(msg) FOR re2 IN ([1],[2],[3],[4],[5],[6],[7])) pvt
[解决办法]有没有动态的我学习一下?
[解决办法]這個我真沒有
[解决办法]