sql-server-2005 – 在Sql Server 2005中将字符串拆分为单个字符

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!