Преобразование нескольких полей с разделителями в строки в SQL Server

У меня есть источник данных, который содержит данные в полях с разделителями, которые существуют в промежуточной области в SQL Server. Я хотел бы преобразовать эти данные во множество строк, чтобы с ними было легче работать. Это отличается от множества других вопросов и ответов по аналогичным темам тем, что у меня есть несколько полей, в которых существуют эти данные с разделителями. Вот пример того, как выглядят мои данные:

 ID | Field | Value
 ---+-------+------
 1  | a,b,c | 1,2,3
 2  | a,c   | 5,2

И это желаемый результат:

ID | Field | Value
---+-------+------    
1  | a     | 1
1  | b     | 2
1  | c     | 3
2  | a     | 5
2  | c     | 2

Мой код до сих пор использует метод синтаксического анализа XML, подобный упомянутому здесь: строка, разделенная запятыми, на отдельные строки. Мне нужно было расширить ее, чтобы соединить каждое поле с соответствующим значением, что я сделал, создав row_number для каждого идентификатора, а затем сопоставив его на основе идентификатора и этого row_number.

Моя проблема в том, что это мучительно медленно, поэтому мне интересно, есть ли у кого-нибудь более эффективные методы?

select      
    [Value].ID, [Field], [Value]
from        
    (select
         A.ID, Split.a.value('.', 'varchar(100)') as [Value],
         row_number() over (partition by ID order by Split.a) as RowNumber
     from 
         (select 
              ID, cast('<M>' + replace([Value], ',', '</M><M>') + '</M>' as xml) as [Value]
          from
              #source_table
          where
              [Field] not like '%[<>&%]%' and [Value] not like '%[<>&%]%') as A
     cross apply
         [Value].nodes ('/M') as Split(a)
    ) [Value]
inner join  
    (
        select
            A.ID, Split.a.value('.', 'varchar(100)') as [Field],
            row_number() over (partition by A.ID order by Split.a) as RowNumber
        from 
            (select
                 ID, cast('<M>' + replace([Field], ',', '</M><M>') + '</M>' as xml) as [Field]
             from  
                 #source_table
             where
                 [Field] not like '%[<>&%]%' and [Value] not like '%[<>&%]%') as A
        cross apply 
            [Field].nodes ('/M') as Split(a)
    ) [Field] on [Value].ID = [Field].ID and [Value].RowNumber = [Field].RowNumber

sql sql-server delimited
person user2323503    schedule 10.10.2017    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Вот подход с использованием сплиттера от Джеффа Модена. http://www.sqlservercentral.com/articles/Tally+Table/72993/ Одной из приятных особенностей этого сплиттера является то, что он возвращает порядковый номер каждого элемента, так что вы можете использовать его для соединений и тому подобного.

Начиная с некоторых данных.

declare @Something table
(
    ID int
    , Field varchar(50)
    , Value varchar(50)
)

insert @Something values
(1, 'a,b,c', '1,2,3')
, (2, 'a,c', '5,2')
;

Поскольку у вас есть два набора данных с разделителями, вам придется разделить их для каждого набора значений с разделителями. Вот как вы можете использовать этот сплиттер для достижения этой цели.

with Fields as
(
    select *
    from @Something s
    cross apply dbo.DelimitedSplit8K(s.Field, ',') f
)
, Value as
(
    select *
    from @Something s
    cross apply dbo.DelimitedSplit8K(s.Value, ',') v
)

select f.ID
    , Field = f.Item
    , Value = v.Item
from Fields f
join Value v on v.ItemNumber = f.ItemNumber and v.ID = f.ID

Если это вообще возможно, было бы лучше посмотреть, можете ли вы изменить какой-либо процесс, который заполняет ваши исходные данные, чтобы он был нормализован, а не ограничен, потому что с ним сложно работать.

person Sean Lange    schedule 10.10.2017

arrow_upward
0
arrow_downward

Основываясь на запросе @Gordon Linoff здесь, еще один рекурсивный cte:

DECLARE @t TABLE(
  ID int
  ,Field VARCHAR(MAX)
  ,Value VARCHAR(MAX)
)

INSERT INTO @t VALUES
(1, 'a,b,c', '1,2,3')
,(2, 'a,c', '5,2')
,(3, 'x', '7');


with cte as (
      select ID
            ,SUBSTRING(Field, 1, CASE WHEN CHARINDEX(',', Field) > 0 THEN CHARINDEX(',', Field)-1 ELSE LEN(Field) END) AS Field
            ,SUBSTRING(Value, 1, CASE WHEN CHARINDEX(',', Value) > 0 THEN CHARINDEX(',', Value)-1 ELSE LEN(Value) END) AS Value
            ,SUBSTRING(Field, CASE WHEN CHARINDEX(',', Field) > 0 THEN CHARINDEX(',', Field)+1 ELSE 1 END, LEN(Field)-CASE WHEN CHARINDEX(',', Field) > 0 THEN CHARINDEX(',', Field) ELSE 0 END) as field_list
            ,SUBSTRING(Value, CASE WHEN CHARINDEX(',', Value) > 0 THEN CHARINDEX(',', Value)+1 ELSE 1 END, LEN(Value)-CASE WHEN CHARINDEX(',', Value) > 0 THEN CHARINDEX(',', Value) ELSE 0 END) as value_list
            ,0 as lev
      from @t
      WHERE CHARINDEX(',', Field) > 0
      UNION ALL
      select ID
            ,SUBSTRING(field_list, 1, CASE WHEN CHARINDEX(',', field_list) > 0 THEN CHARINDEX(',', field_list)-1 ELSE LEN(field_list) END) AS Field
            ,SUBSTRING(value_list, 1, CASE WHEN CHARINDEX(',', value_list) > 0 THEN CHARINDEX(',', value_list)-1 ELSE LEN(value_list) END) AS Value
            ,CASE WHEN CHARINDEX(',', field_list) > 0 THEN SUBSTRING(field_list, CHARINDEX(',', field_list)+1, LEN(field_list)-CHARINDEX(',', field_list)) ELSE '' END as field_list
            ,CASE WHEN CHARINDEX(',', value_list) > 0 THEN SUBSTRING(value_list, CHARINDEX(',', value_list)+1, LEN(value_list)-CHARINDEX(',', value_list)) ELSE '' END as value_list
            ,lev + 1
      from cte
      WHERE LEN(field_list) > 0
     )
select ID, Field, Value
from cte
UNION ALL
SELECT ID, Field, Value
  FROM @t
  WHERE CHARINDEX(',', Field) = 0
ORDER BY ID, Field
OPTION (MAXRECURSION 0)
person Tyron78    schedule 10.10.2017

arrow_upward
0
arrow_downward

Один из методов — рекурсивный CTE:

with cte as (
      select id, cast(NULL as varchar(max)) as field, cast(NULL as varchar(max)) as value, field as field_list, value as value_list, 0 as lev
      from t
      union all
      select id, left(field_list, charindex(',', field_list + ',') - 1),
             left(value_list, charindex(',', value_list + ',') - 1),
             substring(field_list, charindex(',', field_list + ',') + 1, len(field_list)),
             substring(value_list, charindex(',', value_list + ',') + 1, len(value_list)),
             1 + lev
      from cte
      where field_list <> '' and value_list <> ''
     )
select *
from cte
where lev > 0;

Вот пример того, как это работает.

person Gordon Linoff    schedule 10.10.2017
comment
Хм... ваше решение кажется немного ошибочным: оно возвращает идентификаторы, но ни поля, ни значения... скорее всего, потому что вы выбираете NULL в первой итерации, а затем подстроку NULL в следующей... - person Tyron78; 10.10.2017