嗨,我有一个输入
ID data 1 hello 2 sql
期望的输出是
ID RowID Chars 1 1 H 1 2 e 1 3 l 1 4 l 1 5 o 2 1 s 2 2 q 2 3 l
我的方法到目前为止
Declare @t table(ID INT IDENTITY,data varchar(max)) Insert into @t Select 'hello' union all select 'sql' --Select * from @t ;With CteMaxlen As( Select MaxLength = max(len(data)) from @t),Num_Cte AS ( SELECT 1 AS rn UNION ALL SELECT rn +1 AS rn FROM Num_Cte WHERE rn <(select MaxLength from CteMaxlen) ) -- Shred into individual characters,Get_Individual_Chars_Cte AS ( SELECT ID,Row_ID =ROW_NUMBER() Over(PARTITION by ID Order by ID),chars FROM @t,Num_Cte CROSS APPLY( SELECT SUBSTRING((select data from @t),rn,1) AS chars) SplittedChars ) Select * from Get_Individual_Chars_Cte
查询根本不起作用
Msg 512,Level 16,State 1,Line 4
Subquery returned more than 1 value.
This is not permitted when the
subquery follows =,!=,<,<=,>,>=
or when the subquery is used as an
expression.
编辑：
我找到了答案
;with Get_Individual_Chars_Cte AS
(
SELECT
ID,SUBSTRING(Data,Number,1) AS [Char]--,FROM @t
INNER JOIN master.dbo.spt_values ON
Number BETWEEN 1 AND LEN(Data)
AND type='P'
)
Select * from Get_Individual_Chars_Cte
需要帮助
解决方法
;with cte as ( select ID,substring(data,1,1) as Chars,stuff(data,'') as data,1 as RowID from @t union all select ID,RowID + 1 as RowID from cte where len(data) > 0 ) select ID,RowID,Chars from cte order by ID,RowID
