将一条记录划分为多条记录
将一条记录划分为多条记录
提问于 2016-04-22 16:46:14
我需要一个查询来改变这一点：
col1 col2 col3
1 2 abc
3 4 cd
4 5 null到这个
col1 col2 col3
1 2 a
1 2 b
1 2 c
3 4 c
3 4 d
4 5 NULL谢谢。
Stack Overflow用户
发布于 2016-04-22 17:09:50
像这样试一下
为流水号编辑较短的语法
EDIT2 JamieD77的评论将TOP包含在数字的创建中
CREATE FUNCTION dbo.SingleChars(@SomeText NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
WITH nr10 AS
(
SELECT * FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tbl(A)
)
,RunningNumbers AS
(
SELECT TOP (ISNULL(LEN(@SomeText),0)) ROW_NUMBER() OVER(ORDER BY (SELECT(NULL))) AS Nmbr FROM nr10,nr10 AS a,nr10 AS b,nr10 AS c,nr10 AS d,nr10 AS e,nr10 AS f,nr10 AS g
)
SELECT SUBSTRING(@SomeText,Nmbr,1) AS TheCharacter
,ASCII(SUBSTRING(@SomeText,Nmbr,1)) AS ASCII_Code
FROM RunningNumbers;一个测试
SELECT * FROM dbo.SingleChars('This is a test');结果
T 84
h 104
i 105
s 115
32
i 105
s 115
32
a 97
32
t 116
e 101
s 115
t 116现在你的问题来了
DECLARE @tbl TABLE(col1 INT, col2 INT, col3 VARCHAR(100));
INSERT INTO @tbl VALUES(1,2,'abc'),(3,4,'cd'),(4,5,NULL);
SELECT col1,col2,sc.TheCharacter
FROM @tbl
OUTER APPLY dbo.SingleChars(col3) AS sc;结果
1 2 a
1 2 b
1 2 c
3 4 c
3 4 d
4 5 NULL页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36789117
复制相关文章
相似问题
腾讯云开发者
