Sqlserver将表中某列数据以符号分成多行

佚名 6年前 (2019-05-06) 随笔 1574人围观抢沙发百度已收录

WITH testtb2 AS 
(
select b.ActionID,a.VisitorCard,b.FStatus,CHARINDEX(',', A.VisitorCard) AS STA, CHARINDEX(',', A.VisitorCard) - 1 AS LENS From  SecuritySign a,TruckAction b where a.ActionID=b.ActionID and len(VisitorCard)<6 and CHARINDEX(',',a.VisitorCard)>0 
　　UNION ALL
select ActionID,VisitorCard,FStatus,CHARINDEX(',', VisitorCard, STA + 1) AS STA, CHARINDEX(',', VisitorCard, STA + 1) - STA - 1 AS LENS From  testtb2 where (STA <> 0) 
)
SELECT TOP (100) PERCENT SUBSTRING(VisitorCard, STA - LENS, LENS) AS OrderRequest,  * 
FROM testtb2 AS testtb2_1
WHERE (STA <> 0)
ORDER BY VisitorCard