Использование string_split для создания строк из нескольких столбцов
У меня есть данные, которые выглядят примерно так (к сожалению, в гораздо большем масштабе):
+----+-------+--------------------+-----------------------------------------------+
| ID | Data | Cost | Comments |
+----+-------+--------------------+-----------------------------------------------+
| 1 | 1|2|3 | $0.00|$3.17|$42.42 | test test||previous thing has a blank comment |
+----+-------+--------------------+-----------------------------------------------+
| 2 | 1 | $420.69 | test |
+----+-------+--------------------+-----------------------------------------------+
| 3 | 1|2 | $3.50|$4.20 | |test |
+----+-------+--------------------+-----------------------------------------------+
Некоторые столбцы в имеющейся у меня таблице разделены конвейером, но они согласованы для каждой строки. Таким образом, каждое значение с разделителями соответствует тому же индексу в других столбцах той же строки.
Итак, я могу сделать что-то вроде этого, что я хочу для одного столбца:
SELECT ID, s.value AS datavalue
FROM MyTable t CROSS APPLY STRING_SPLIT(t.Data, '|') s
и это дало бы мне это:
+----+-----------+
| ID | datavalue |
+----+-----------+
| 1 | 1 |
+----+-----------+
| 1 | 2 |
+----+-----------+
| 1 | 3 |
+----+-----------+
| 2 | 1 |
+----+-----------+
| 3 | 1 |
+----+-----------+
| 3 | 2 |
+----+-----------+
но я также хочу получить и другие столбцы (стоимость и комментарии в этом примере), чтобы все соответствующие элементы находились в одной строке, например:
+----+-----------+-----------+------------------------------------+
| ID | datavalue | costvalue | commentvalue |
+----+-----------+-----------+------------------------------------+
| 1 | 1 | $0.00 | test test |
+----+-----------+-----------+------------------------------------+
| 1 | 2 | $3.17 | |
+----+-----------+-----------+------------------------------------+
| 1 | 3 | $42.42 | previous thing has a blank comment |
+----+-----------+-----------+------------------------------------+
| 2 | 1 | $420.69 | test |
+----+-----------+-----------+------------------------------------+
| 3 | 1 | $3.50 | |
+----+-----------+-----------+------------------------------------+
| 3 | 2 | $4.20 | test |
+----+-----------+-----------+------------------------------------+
Я не уверен, какой лучший или самый простой способ добиться этого был бы
Ответы 2
Это не будет достижимо с STRING_SPLIT, поскольку Microsoft отказывается предоставлять порядковый номер как часть набора результатов. В результате вам нужно будет использовать другую функцию, которая работает. Лично я рекомендую Джеффа Модена DelimitedSplit8k.
Затем вы можете сделать это:
CREATE TABLE #Sample (ID int,
[Data] varchar(200),
Cost varchar(200),
Comments varchar(8000));
GO
INSERT INTO #Sample
VALUES (1,'1|2|3','$0.00|$3.17|$42.42','test test||previous thing has a blank comment'),
(2,'1','$420.69','test'),
(3,'1|2','$3.50|$4.20','|test');
GO
SELECT S.ID,
DSd.Item AS DataValue,
DSc.Item AS CostValue,
DSct.Item AS CommentValue
FROM #Sample S
CROSS APPLY dbo.DelimitedSplit8K(S.[Data],'|') DSd
CROSS APPLY (SELECT *
FROM DelimitedSplit8K(S.Cost,'|') SS
WHERE SS.ItemNumber = DSd.ItemNumber) DSc
CROSS APPLY (SELECT *
FROM DelimitedSplit8K(S.Comments,'|') SS
WHERE SS.ItemNumber = DSd.ItemNumber) DSct;
GO
DROP TABLE #Sample;
GO
Однако есть только один верный ответ на этот вопрос: Не храните значения с разделителями в SQL Server. Сохраните их в нормализованном виде, и у вас не будет этой проблемы.
Да, работать с такими данными не так уж и весело. Сделайте так, чтобы API в основном передавал нам данные. Мы проверим это решение подробнее. Спасибо! (Возможно, вы просто вытащите все данные из БД и разделите их с помощью сценария, чтобы правильно вставить)
@SeanLange Я искренне ожидал, что MS обновит функцию до 2017 года, чтобы предоставить позицию. Я был действительно разочарован ими, когда они этого не сделали, так как спрос на функциональность со стороны сообществ был довольно высоким.
Вот подход к решению с использованием рекурсивного CTE вместо и определяемой пользователем функции (UDF), который полезен для тех, у кого нет разрешения на создание функций.
CREATE TABLE mytable(
ID INTEGER NOT NULL PRIMARY KEY
,Data VARCHAR(7) NOT NULL
,Cost VARCHAR(20) NOT NULL
,Comments VARCHAR(47) NOT NULL
);
INSERT INTO mytable(ID,Data,Cost,Comments) VALUES (1,'1|2|3','$0.00|$3.17|$42.42','test test||previous thing has a blank comment');
INSERT INTO mytable(ID,Data,Cost,Comments) VALUES (2,'1','$420.69','test');
INSERT INTO mytable(ID,Data,Cost,Comments) VALUES (3,'1|2','$3.50|$4.20','|test');
Этот запрос позволяет выбрать разделитель с помощью переменной, а затем, используя общее табличное выражение, он анализирует каждую строку с разделителями для создания строк для каждой части этих строк и сохраняет порядковый номер каждой.
declare @delimiter as varchar(1)
set @delimiter = '|'
;with cte as (
select id
, convert(varchar(max), null) as datavalue
, convert(varchar(max), null) as costvalue
, convert(varchar(max), null) as commentvalue
, convert(varchar(max), data + @delimiter) as data
, convert(varchar(max), cost + @delimiter) as cost
, convert(varchar(max), comments + @delimiter) as comments
from mytable as t
union all
select id
, convert(varchar(max), left(data, charindex(@delimiter, data) - 1))
, convert(varchar(max), left(cost, charindex(@delimiter, cost) - 1))
, convert(varchar(max), left(comments, charindex(@delimiter, comments) - 1))
, convert(varchar(max), stuff(data, 1, charindex(@delimiter, data), ''))
, convert(varchar(max), stuff(cost, 1, charindex(@delimiter, cost), ''))
, convert(varchar(max), stuff(comments, 1, charindex(@delimiter, comments), ''))
from cte
where (data like ('%' + @delimiter + '%') and cost like ('%' + @delimiter + '%')) or comments like ('%' + @delimiter + '%')
)
select id, datavalue, costvalue, commentvalue
from cte
where datavalue IS NOT NULL
order by id, datavalue
По мере того, как рекурсия добавляет новые строки, она помещает первую часть строк с разделителями в желаемые выходные столбцы с помощью left(), а затем также, используя stuff(), удаляет последний использованный разделитель из исходных строк, чтобы следующая строка начиналась со следующего разделителя. . Обратите внимание, что для инициирования извлечения в конец строк с разделителями источника добавляется разделитель, чтобы гарантировать, что предложение where не исключает ни одну из требуемых строк.
результат:
id datavalue costvalue commentvalue
---- ----------- ----------- ------------------------------------
1 1 $0.00 test test
1 2 $3.17
1 3 $42.42 previous thing has a blank comment
2 1 $420.69 test
3 1 $3.50
3 2 $4.20 test
продемонстрировал здесь на dbfiddle.uk
Порядковый номер так важен. Меня всегда шокирует, что Jeff Moden's - единственный жизнеспособный сплиттер, который включает его. Большинство разделителей XML, по крайней мере, возвращают их в том же порядке, но я предпочитаю быть явным, а не предполагать, что он работает.