使用FOR XML路径的SQL Server中的字符串连接问题。 [英] Problem in string concatenation in sql server using FOR XML Path.

问题描述

我有以下数据

UniqueID    ID  data
1       1   a
2       1   2
3       1   b
4       1   1
5       2   d
6       2   3
7       2   r

预期输出为

ID  ConcatData
1   a,-,-,b,-
2   d,-,-,-,r

我们要做的是，必须用许多破折号（'-'）代替数字字符的数量，然后我们需要合并对应ID的数据。

What we have to do is that, the number of numeric charecters has to be replaced with those many dashes('-') and then we need to merge the data for the respective id's.

到目前为止，我正在使用以下查询

I am using the below query so far

declare @t table(UniqueID int identity(1,1), ID int, data varchar(10))  
insert into @t select 1, 'a' union all  select 1, '2' union all select 1, 'b' 
union all select 1, '1' union all select 2, 'd' union all select 2, '3' 
union all select 2, 'r' 

select * from @t

;with cte1 as 
(     
    select 
        UniqueId
        , id
        , data
        , case when isnumeric(data) = 1 then cast(data as int) end Level
         from @t     
    union all     
    select 
        UniqueId
        , id
        , CAST('-' as varchar(10))
        , Level - 1     
    from cte1     
    where Level > 0 ) 
,cte2 as
(
select id, GroupID = Dense_Rank() Over(Order by id),data, DataOrder = ROW_NUMBER() over(order by UniqueID, Level)
from cte1 
where Level is null or data = '-' 
) 

SELECT
ID
, (select data + ',' 
from cte2 t2 
where t2.GroupID = t1.GroupID
for XML path('')
) as ConcatData
from cte2 t1
group by t1.ID ,t1.GroupID

但输出为

ID  ConcatData
1   a,b,-,-,-,
2   d,r,-,-,-,

那是我不能在字符之间放置破折号（'-'）。

That is I am not able to position the dashes('-') in between the characters.

请帮助

推荐答案

尝试一下：

;with cte1 as
(
select 
UniqueId
, id
, data
,case when isnumeric(data) = 1 
    THEN replicate(',-',data) 
    ELSE ',' + data end as string
from @t
)

select 
id
,LTRIM(STUFF(
    (
    SELECT
      ' ' + t2.String
    FROM Cte1 t2
    WHERE t2.id = t1.id
    FOR XML PATH('')
    ), 2, 1, ''
  )) As concatenated_string
from cte1 t1 group by t1.ID ,t1.ID 

cte1 t1组，适用于示例数据bove，可能是比使用游标要快

Works for the sample data bove and might be a bit quicker than using cursors

这篇关于使用FOR XML路径的SQL Server中的字符串连接问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！