22,209
社区成员
发帖
与我相关
我的任务
分享
DECLARE @s NVARCHAR(MAX)
DECLARE @fid INT=1
;WITH a(s) AS (
SELECT N'1|欠费拆表 2|空 房 3|正常使用 4|水表报停 5|临时用水'
)
SELECT replace(( --合并
SELECT n.ss+'-' FROM (
SELECT s,CONVERT(XML,'<r><n>'+REPLACE(a.s,' ','</n><n>')+'</n></r>') AS x FROM a
) t
CROSS APPLY (
SELECT SUM(CASE WHEN ISNUMERIC( LEFT(a.ss,1))=1 THEN 1 ELSE 0 END)OVER( order by a.seq ) AS g
,convert(int,MAX(CASE WHEN ISNUMERIC( LEFT(a.ss,1))=1 THEN LEFT(a.ss,1) ELSE '' END)OVER( order by a.seq )) AS id
,a.* FROM (
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS seq, b.value('.','NVARCHAR(500)') AS ss FROM t.x.nodes('r/n') s(b)
) a
) n
WHERE n.id=2
FOR XML PATH('')
),'-',' ')