Проблема в конкатенации строк на сервере sql с использованием 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

Что нам нужно сделать, так это то, что количество числовых символов должно быть заменено этим количеством дефисов ('-'), а затем нам нужно объединить данные для соответствующих идентификаторов.

Я использую приведенный ниже запрос до сих пор

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,-,-,-,

То есть я не могу поставить тире ('-') между символами.

Пожалуйста помоги


sql sql-server tsql common-table-expression sql-server-2005
person aditi    schedule 16.06.2011    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Попробуй это:

;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

Работает для примера данных выше и может быть немного быстрее, чем использование курсоров.

person Dibstar    schedule 16.06.2011
comment
Спасибо, узнал что-то новое о репликации. Согласен, Ваше предложение лучше. Спасибо :) - person Pankaj; 16.06.2011

arrow_upward
0
arrow_downward

Ниже приведена таблица создания

Create table #temp
(
    IDUnique int Identity(1,1),
    ID int, 
    data varchar(100)
)

Ниже приведены записи, предложенные вами.

Insert into #temp(ID, data) Values(1, 'a')
Insert into #temp(ID, data) Values(1, '2')
Insert into #temp(ID, data) Values(1, 'b')
Insert into #temp(ID, data) Values(1, '1')
Insert into #temp(ID, data) Values(2, 'd')
Insert into #temp(ID, data) Values(2, '3')
Insert into #temp(ID, data) Values(2, 'r')

введите здесь описание изображения

Ниже приведена реализация курсора.

declare @IDUnique int
declare @ID int
declare  @data varchar(100)
declare @Latest int
declare @Previous int
declare @Row int

set @Latest = 1
set @Previous = 1

Create Table #temp1
(
    ID int,
    data varchar(100)
)

--SELECT Row_Number() Over(Order by IDUnique) Row, IDUnique, ID, data  From #temp


DECLARE @getAccountID CURSOR SET @getAccountID = CURSOR FOR SELECT Row_Number() Over(Order by IDUnique) Row, IDUnique, ID, data  From #temp
OPEN @getAccountID
FETCH NEXT FROM @getAccountID INTO @Row, @IDUnique, @ID, @data
WHILE @@FETCH_STATUS = 0
BEGIN
    IF(@Row = 1)
    Begin
        Set @Previous = @ID
        Set @Latest = @ID
        Insert into #temp1(ID, data)values(@Previous, @data)
    End
    Else If (@Previous <> @ID)
    Begin
        Set @Previous = @ID
        Set @Latest = @ID
        Insert into #temp1(ID, data)values(@Previous, @data)
    End
    Else
    Begin
        Declare @number int
        if(ISNUMERIC(@data) = 1)
        Begin
            Set @number = Convert(int , @data)
            While(@number <> 0)
            Begin
                Update #temp1 Set Data  = Data + ',-' Where ID = @ID
                Set @number = @number - 1
            End
        End
        Else    
        begin
            Update #temp1 Set Data  = Data + ',' + @data Where ID = @ID
        End
    End

    FETCH NEXT FROM @getAccountID INTO @Row, @IDUnique, @ID, @data
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

Select * from #temp1
Select * from #temp
Drop Table #temp

вот окончательный набор результатов

введите здесь описание изображения

person Pankaj    schedule 16.06.2011
comment
Вы выполняете фильтрацию числовых и других числовых значений. Поэтому вывод не правильный. Чтобы двигаться последовательно, я думаю, вы должны двигаться с помощью курсора. - person Pankaj; 16.06.2011