Разбиение строки с разделителем на столбцы
У таблицы есть колонка TestResult , в которой хранится результаты участников (в %) по некоторым показателям:
DECLARE @TestResults TABLE(ParticipCode INT, Elements VARCHAR(100)) INSERT INTO @TestResults VALUES (1, '0,50;0,20;0,30') INSERT INTO @TestResults VALUES (2, '0,60;0,24;0,32') НЕОБХОДИМО
Необходимо получить среднее значение по каждому показателю, т.е.: 
И нет разницы в каком виде они будут представлены в результирующем наборе — по строкам 
или столбцам 
Как получить такое? После того как получится составить скрипт, я хочу на его основе сделать хранимую процедуру, которая принимала бы такой массив и вычисляла средние.
Попытки
1. Работаю в SQL Server 2016 и естественно я решил прибегнуть к новшеству STRING_SPLIT . Но к сожалению моих знаний и попыток хватило только на умение распарсить одну строку:
DECLARE @str VARCHAR(MAX) = (SELECT TOP 1 Elements FROM @TestResults); SELECT * FROM STRING_SPLIT(@str, ';') 
2. Также на форуме нашел решение, которое основано на встроенной функции PARSENAME . Оно не подходит, т.к. эта функция перестанет работать с более чем 4 показателей. У меня в примере 3, но в реальности их больше.
Как собрать строки через разделитель sql
Для объединения строк через разделитель в SQL можно использовать функцию GROUP_CONCAT() . Эта функция объединяет значения строк, указанных столбцов в группе, и возвращает результат в виде одной строки, где значения разделены указанным разделителем.
Вот пример использования GROUP_CONCAT() в MySQL:
SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM users WHERE age > 18; В этом примере мы объединяем значения столбца name из таблицы users, где возраст age больше 18 лет. Разделитель между значениями строк задается ключевым словом SEPARATOR (в данном случае — запятой с пробелом).
Результатом выполнения этого запроса будет одна строка, содержащая все имена пользователей, удовлетворяющих условию, и разделенных запятой с пробелом.
Важно отметить, что некоторые СУБД могут иметь свои собственные способы объединения строк через разделитель. Например, в Oracle для этой цели используется функция LISTAGG() .
Собираем строки через разделитель — STRING_AGG
Часто стоит задача по набору записей в таблице собрать строчку, перечислив значения через разделитель. Ярким примером может служить список адресов электронной почты для рассылки: ‘ivanov_ii@gmail.com; petrov_pp@mail.ru; sidorov_ss@yandex.ru’ .
Решить подобную задачу с помощью SQL легко. Достаточно воспользоваться функцией STRING_AGG . Она доступна и как агрегатная функция, и как оконная.
string_agg (выражение, разделитель [ORDER BY порядок сортировки значений]) Агрегатная функция
Сформируем список товаров в каждом каталоге:
SELECT c.name AS category_name, string_agg(p.name, ', ' ORDER BY p.name) AS products FROM category c, product p WHERE p.category_id = c.category_id GROUP BY c.category_id, c.name | # | category_name | products |
|---|---|---|
| 1 | Бытовая техника | Пылесос S6, Холодильник A2 |
| 2 | Фотоаппараты | Lord Nikon 95, Nikon D750 |
| 3 | Игровые консоли | Nintendo, PlayStation, Xbox |
| 4 | Аудиотехника | Наушники S3 |
| 5 | Сотовые телефоны | Моноблок C4, Слайдер B3 |
| 6 | Ноутбуки | Ультрабук X5 |
| 7 | Рюкзаки | Deepbox |
Как получается такой результат?
Давай разбираться по порядку. Во-первых, посмотрим что получается после соединения таблиц
SELECT c.category_id, c.name AS category_name, p.name AS product_name FROM category c, product p WHERE p.category_id = c.category_id ORDER BY c.category_id, c.name, p.name | # | category_id | category_name | product_name |
|---|---|---|---|
| 1 | 3 | Бытовая техника | Пылесос S6 |
| 2 | 3 | Бытовая техника | Холодильник A2 |
| 3 | 5 | Фотоаппараты | Lord Nikon 95 |
| 4 | 5 | Фотоаппараты | Nikon D750 |
| 5 | 6 | Игровые консоли | Nintendo |
| 6 | 6 | Игровые консоли | PlayStation |
| 7 | 6 | Игровые консоли | Xbox |
| 8 | 7 | Аудиотехника | Наушники S3 |
| 9 | 8 | Сотовые телефоны | Моноблок C4 |
| 10 | 8 | Сотовые телефоны | Слайдер B3 |
| 11 | 9 | Ноутбуки | Ультрабук X5 |
| 12 | 10 | Рюкзаки | Deepbox |
В запросе после WHERE написан GROUP BY . Значит в результате запроса будет столько строк, сколько встретилось уникальных значений перечисленных столбцов:
| # | category_id | category_name |
|---|---|---|
| 1 | 3 | Бытовая техника |
| 2 | 5 | Фотоаппараты |
| 3 | 6 | Игровые консоли |
| 4 | 7 | Аудиотехника |
| 5 | 8 | Сотовые телефоны |
| 6 | 9 | Ноутбуки |
| 7 | 10 | Рюкзаки |
Осталось разобраться с
SELECT c.name AS category_name, string_agg(p.name, ', ' ORDER BY p.name) AS products С «c.name AS category_name» разбираться нечего. Это поле у нас есть в GROUP BY .
Смотрим на «string_agg(p.name, ‘, ‘ ORDER BY p.name) AS products» . Так как over отсутсвует, значит это не оконная функция. Значит значение будет вычисляться в процессе группировки строк.
Внутри string_agg написано: «p.name, ‘, ‘ ORDER BY p.name» . В переводе на русский это звучит так: для каждой группы из GROUP BY возьми все строки, отсортируй их по p.name и соедини названия продуктов p.name через запятую с пробелом ‘, ‘ .
| # | category_id | category_name | product_name | | -: | ---------: | :------------ | :---------- | | 1 | 3 | Бытовая техника | Пылесос S6 | | 2 | 3 | Бытовая техника | Холодильник A2 | | # | category_name | products |
|---|---|---|
| 1 | Бытовая техника | Пылесос S6, Холодильник A2 |
Оконная агрегатная функция
В виде оконной функции STRING_AGG используют довольно редко (кому она такая вообще нужна?). Но мы попробуем)
SELECT c.category_id, c.name AS category_name, p.name AS product_name, string_agg(p.name, ', ') over (PARTITION BY c.category_id) AS product_list FROM category c, product p WHERE p.category_id = c.category_id ORDER BY c.category_id, c.name, p.name | # | category_id | category_name | product_name | product_list |
|---|---|---|---|---|
| 1 | 3 | Бытовая техника | Пылесос S6 | Пылесос S6, Холодильник A2 |
| 2 | 3 | Бытовая техника | Холодильник A2 | Пылесос S6, Холодильник A2 |
| 3 | 5 | Фотоаппараты | Lord Nikon 95 | Nikon D750, Lord Nikon 95 |
| 4 | 5 | Фотоаппараты | Nikon D750 | Nikon D750, Lord Nikon 95 |
| 5 | 6 | Игровые консоли | Nintendo | Xbox, Nintendo, PlayStation |
| 6 | 6 | Игровые консоли | PlayStation | Xbox, Nintendo, PlayStation |
| 7 | 6 | Игровые консоли | Xbox | Xbox, Nintendo, PlayStation |
| 8 | 7 | Аудиотехника | Наушники S3 | Наушники S3 |
| 9 | 8 | Сотовые телефоны | Моноблок C4 | Слайдер B3, Моноблок C4 |
| 10 | 8 | Сотовые телефоны | Слайдер B3 | Слайдер B3, Моноблок C4 |
| 11 | 9 | Ноутбуки | Ультрабук X5 | Ультрабук X5 |
| 12 | 10 | Рюкзаки | Deepbox | Deepbox |
В целом, результат получился весьма ожидаемый. Но есть одно но. мы не указали сортировку при формировании product_list .
Сделаем глупость, добавим ее в over :
SELECT c.category_id, c.name AS category_name, p.name AS product_name, string_agg(p.name, ', ') over (PARTITION BY c.category_id ORDER BY p.name) AS product_list FROM category c, product p WHERE p.category_id = c.category_id ORDER BY c.category_id, c.name, p.name | # | category_id | category_name | product_name | product_list |
|---|---|---|---|---|
| 1 | 3 | Бытовая техника | Пылесос S6 | Пылесос S6 |
| 2 | 3 | Бытовая техника | Холодильник A2 | Пылесос S6, Холодильник A2 |
| 3 | 5 | Фотоаппараты | Lord Nikon 95 | Lord Nikon 95 |
| 4 | 5 | Фотоаппараты | Nikon D750 | Lord Nikon 95, Nikon D750 |
| 5 | 6 | Игровые консоли | Nintendo | Nintendo |
| 6 | 6 | Игровые консоли | PlayStation | Nintendo, PlayStation |
| 7 | 6 | Игровые консоли | Xbox | Nintendo, PlayStation, Xbox |
| 8 | 7 | Аудиотехника | Наушники S3 | Наушники S3 |
| 9 | 8 | Сотовые телефоны | Моноблок C4 | Моноблок C4 |
| 10 | 8 | Сотовые телефоны | Слайдер B3 | Моноблок C4, Слайдер B3 |
| 11 | 9 | Ноутбуки | Ультрабук X5 | Ультрабук X5 |
| 12 | 10 | Рюкзаки | Deepbox | Deepbox |
Как и ожидалось, результат получился не таким, какой хотели получить. В предыдущих заданиях рассказано, почему так.
Правильно указывать сортировка внутри самой string_agg :
SELECT c.category_id, c.name AS category_name, p.name AS product_name, string_agg(p.name, ', ' ORDER BY p.name) over (PARTITION BY c.category_id) AS product_list FROM category c, product p WHERE p.category_id = c.category_id ORDER BY c.category_id, c.name, p.name error: aggregate ORDER BY is not implemented for window functions Ну вот. Фича не реализована в PostgreSQL 🙁
Как разбить строку на символ с разделителями в SQL Server?
В этой статье мы обсудим несколько способов разделения строкового значения с разделителями. Это может быть достигнуто с использованием нескольких методов, в том числе.
- Использование функции STRING_SPLIT для разделения строки
- Создайте пользовательскую функцию с табличным значением для разделения строки,
- Используйте XQuery для разделения строкового значения и преобразования строки с разделителями в XML.
Прежде всего, нам нужно создать таблицу и вставить в нее данные, которые будут использоваться во всех трех методах. Таблица должна содержать одну строку с идентификатором поля и строку с символами-разделителями. Создайте таблицу с именем «student», используя следующий код.
Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)
CREATE TABLE student (ID INT IDENTITY (1, 1), student_name VARCHAR(MAX))
Вставьте имена учащихся, разделенные запятыми, в одну строку, выполнив следующий код.
ВСТАВЬТЕ В ЦЕННОСТИ студента (имя_ученика) («Монрой, Монтаньес, Маролахакис, Негли, Олбрайт, Гарофоло, Перейра, Джонсон, Вагнер, Конрад»)Создание таблицы и вставка данных
Проверьте, были ли данные вставлены в таблицу или нет, используя следующий код.
выберите * от студентаПроверьте, были ли данные вставлены в таблицу «студент»
Способ 1: используйте функцию STRING_SPLIT для разделения строки
В SQL Server 2016 была представлена функция «STRING_SPLIT», которую можно использовать с уровнем совместимости 130 и выше. Если вы используете версию SQL Server 2016 или более позднюю, вы можете использовать эту встроенную функцию.
Кроме того, «STRING_SPLIT» вводит строку с разделенными подстроками и вводит один символ для использования в качестве разделителя или разделителя. Функция выводит таблицу с одним столбцом, строки которой содержат подстроки. Имя выходного столбца «Значение». Эта функция получает два параметра. Первый параметр — это строка, а второй — символ-разделитель или разделитель, на основе которого мы должны разделить строку. Вывод содержит таблицу с одним столбцом, в которой присутствуют подстроки. Этот выходной столбец называется «Значение», как мы можем видеть на рисунке ниже. Кроме того, функция table_value «STRING SPLIT» возвращает пустую таблицу, если входная строка равна NULL.
Уровень совместимости базы данных:
Каждая база данных связана с уровнем совместимости. Он обеспечивает совместимость поведения базы данных с конкретной версией SQL Server, на которой она работает.
Теперь мы будем вызывать функцию «string_split», чтобы разделить строку, разделенную запятыми. Но уровень совместимости был меньше 130, поэтому возникла следующая ошибка. “Недопустимое имя объекта “SPLIT_STRING””
Ошибка возникает, если уровень совместимости базы данных ниже 130 «Недопустимое имя объекта split_string»
Таким образом, нам нужно установить уровень совместимости базы данных 130 или выше. Поэтому мы выполним этот шаг, чтобы установить уровень совместимости базы данных.
- Прежде всего установите базу данных в «single_user_access_mode», используя следующий код.
ИЗМЕНИТЬ БАЗУ ДАННЫХ УСТАНОВИТЬ ОДНОГО_ПОЛЬЗОВАТЕЛЯ
- Во-вторых, измените уровень совместимости базы данных, используя следующий код.
ИЗМЕНИТЬ БАЗУ ДАННЫХ УСТАНОВИТЬ СОВМЕСТИМОСТЬ_УРОВЕНЬ = 130
- Верните базу данных в режим многопользовательского доступа, используя следующий код.
ИЗМЕНИТЬ БАЗУ ДАННЫХ УСТАНОВИТЬ МНОГОПОЛЬЗОВАТЕЛЬСКИЙ [master]
ПЕРЕЙТИ ИЗМЕНИТЬ БАЗУ ДАННЫХ [bridge_centrality] УСТАНОВИТЬ ОДНОГО ПОЛЬЗОВАТЕЛЯ ИЗМЕНИТЬ БАЗУ ДАННЫХ [bridge_centrality] УСТАНОВИТЬ COMPATIBILITY_LEVEL = 130 ИЗМЕНИТЬ БАЗУ ДАННЫХ [bridge_centrality] УСТАНОВИТЬ МНОГОПОЛЬЗОВАТЕЛЬСКИЙ GO
Измените уровень совместимости на 130
Теперь запустите этот код, чтобы получить требуемый результат.
DECLARE @string_value VARCHAR(MAX) ; SET @string_value=”Монрой,Монтанес,Маролахакис,Негли,Олбрайт,Гарофоло,Перейра,Джонсон,Вагнер,Конрад” SELECT * FROM STRING_SPLIT(@string_value, ‘,’)
Вывод для этого запроса будет:
Вывод из функции build_in «split_string»
Способ 2. Чтобы разделить строку, создайте определяемую пользователем функцию с табличным значением.
Безусловно, этот традиционный метод поддерживается всеми версиями SQL Server. В этом методе мы создадим определяемую пользователем функцию для разделения строки по символу-разделителю, используя функцию «SUBSTRING», «CHARINDEX» и цикл while. Эту функцию можно использовать для добавления данных в выходную таблицу, поскольку ее тип возвращаемого значения — «таблица».
СОЗДАТЬ ФУНКЦИЮ [dbo].[split_string]
( @string_value NVARCHAR(MAX), @delimiter_character CHAR(1) ) RETURNS @result_set TABLE(splited_data NVARCHAR(MAX)) BEGIN DECLARE @start_position INT, @ending_position INT SELECT @start_position = 1, @ending_position = CHARINDEX(@delimiter_character, @ string_value) WHILE @start_position ‘ + Заменить(@string_value, @delimiter_value, ‘ ‘) + ‘ ‘ ) AS XML) SELECT @xml_value
Вывод для этого запроса будет:
Шаг 1 для разделения строки с помощью XML
Если вы хотите просмотреть весь файл XML. Нажмите на ссылку. После того, как вы нажмете, код ссылки будет выглядеть так.
XML-файл, содержащий отдельные узлы строки для разделения
Теперь XML-строка должна быть обработана дальше. Наконец, мы будем использовать «x-Query» для запроса из XML.
DECLARE @xml_value КАК XML, @string_value КАК VARCHAR(2000), @delimiter_value КАК VARCHAR(15) SET @string_value=(ВЫБЕРИТЕ имя_ученика ИЗ ученика) ЗАДАТЬ @delimiter_value=”,” SET @xml_value = Cast(( ‘ ‘ + Заменить(@string_value, @delimiter_value, ‘ ‘) + ‘ ‘ ) AS XML) SELECT xmquery(‘.’).value(‘.’, ‘VARCHAR(15)’ ) КАК ЗНАЧЕНИЕ ИЗ @xml_value.nodes(‘/studentname’) КАК x(m)
Вывод будет таким:
Использование «XQuery» для запросов из XML
Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)