SQL — разделение строки на несколько столбцов

Я хотел бы разделить результат запроса и отобразить значения в отдельных столбцах. В качестве примера я получаю следующий результат

|Name          |
|ABC_DEFG_HIJKL|
|A_B_C         |
|A_B_C_D       |

Я хочу разделить значения на «_» и добавить их в отдельные столбцы. Результат запроса должен выглядеть примерно так

|Name          |first   |second   |third   |fourth|
|ABC_DEFG_HIJKL|ABC     |DEFG     |HIJKL   |null  |
|A_B_C         |A       |B        |C       |null  |
|A_B_C_D       |A       |B        |C       |D     |

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

SELECT DP.Name, value  
FROM RitopDatenpunkt DP  
    CROSS APPLY STRING_SPLIT(DP.Name, '_'); 


|Name          |value   |
|ABC_DEFG_HIJKL|ABC     |
|ABC_DEFG_HIJKL|DEFG    |
|ABC_DEFG_HIJKL|HIJKL   |
|A_B_C         |A       |
|A_B_C         |B       |
|A_B_C         |C       |
|A_B_C_D       |A       |
|A_B_C_D       |B       |
|A_B_C_D       |C       |
|A_B_C_D       |D       |

Я знаю, что должен использовать PIVOT. Но какую агрегирующую функцию я использую и являются ли аргументы для оператора FOR правильными?

SELECT DP.Name, value  
FROM RitopDatenpunkt DP  
    CROSS APPLY STRING_SPLIT(DP.Name, '_')
PIVOT
(
        GROUPING(Name) as Name
        FOR value in ([first],[second],[third],[fourth])
)piv;

sql string sql-server sql-server-2019 split
person riflex    schedule 25.10.2020    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Вот один из способов сделать это с помощью функций JSON:

select t.name,
    json_value(x.obj, '$[0]') name1,
    json_value(x.obj, '$[1]') name2,
    json_value(x.obj, '$[2]') name2,
    json_value(x.obj, '$[3]') name4
from mytable t
cross apply (values('["' + replace(t.name, '_', '", "') + '"]')) x(obj)

Хитрость заключается в том, чтобы манипулировать строкой, чтобы она выглядела как массив JSON (это то, что делает подзапрос cross apply). По сути, это превращает строку типа 'A_B_C' в '["A", "B", "C"]'. Затем мы можем использовать json_value() для простого доступа к каждому отдельному элементу.

Это не предполагает ничего об уникальности элементов. На самом деле единственное требование состоит в том, что строка не должна содержать встроенных двойных кавычек.

Демонстрация DB Fiddle:

name           | name1 | name2 | name2 | name4
:------------- | :---- | :---- | :---- | :----
ABC_DEFG_HIJKL | ABC   | DEFG  | HIJKL | null 
A_B_C          | A     | B     | C     | null 
A_B_C_D        | A     | B     | C     | D
person GMB    schedule 25.10.2020

arrow_upward
1
arrow_downward

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

SELECT dp.*, s.*
FROM RitopDatenpunkt DP CROSS APPY 
     (SELECT MAX(CASE WHEN SEQNUM = 1 THEN s.value END) as first,
             MAX(CASE WHEN SEQNUM = 2 THEN s.value END) as second,
             MAX(CASE WHEN SEQNUM = 3 THEN s.value END) as third,
             MAX(CASE WHEN SEQNUM = 4 THEN s.value END) as fourth
      FROM (SELECT s.*,
                   ROW_NUMBER() OVER (ORDER BY CHARINDEX('_' + s.value + '_', '_' + DP.Name + '_')) as seqnum
            FROM STRING_SPLIT(DP.Name, '_') s
           ) s
     ) s;

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

К сожалению, STRING_SPLIT() не гарантирует порядок. Альтернативным подходом является использование рекурсивного CTE или неправильное использование PARSENAME(), если у вас не более четырех компонентов.

person Gordon Linoff    schedule 25.10.2020

arrow_upward
0
arrow_downward

Вы также можете использовать PARSENAME() как:

SELECT Val,
       PARSENAME(Value, 1) Name1,
       PARSENAME(Value, 2) Name2,
       PARSENAME(Value, 3) Name3,
       PARSENAME(Value, 4) Name4
FROM
(
  VALUES
  ('ABC_DEFG_HIJKL'), 
  ('A_B_C'),
  ('A_B_C_D')
) T(Val) CROSS APPLY (VALUES(REPLACE(Val, '_', '.'))) TT(Value);

Обратите внимание, что это не сработает, если у вас более 3 '_'.

person Ilyes    schedule 25.10.2020