Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как значения в строке разделённые ";" превратить в столбец?  [new]
MAULER
Member

Откуда:
Сообщений: 864
Здравствуйте.

Есть таблица, со столбцами col1 и col2 в которых хранятся длинные строки.
Каждая длинная строка представляет собой, даты разделенные точкой с запятой.

Возможно ли как то эти длинные строки превратить в столбец значений дат?
По сути обратная операция "for xml path"

Вот что нужно в итоге:

Login col1 col2
DOMAIN\ivanov 2019-02-01;2019-02-05;2019-02-112019-02-22;2019-02-24
DOMAIN\petrov 2019-03-10;2019-01-152019-02-12;2019-07-22;2019-05-22;2019-02-02


Login col move add
DOMAIN\ivanov2019-02-01 1 0
DOMAIN\ivanov2019-02-05 1 0
DOMAIN\ivanov2019-02-11 1 0
DOMAIN\ivanov2019-02-22 0 1
DOMAIN\ivanov2019-02-24 0 1
DOMAIN\petrov2019-03-10 1 0
DOMAIN\petrov2019-01-15 1 0
DOMAIN\petrov2019-02-12 0 1
DOMAIN\petrov2019-07-22 0 1
DOMAIN\petrov2019-05-22 0 1
DOMAIN\petrov2019-02-02 0 1
26 фев 19, 13:03    [21819624]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
MAULER,

STRING_SPLIT
26 фев 19, 13:04    [21819627]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
MAULER,

declare @t table (id int, txt varchar(1000))

insert @t SELECT 1, '2019-02-01;2019-02-05;2019-02-11'

SELECT id,T2.Loc.query('.').value('(/row/@txt)[1]' , 'varchar(10)')  
from
(
	SELECT id, convert(xml, '<row txt="' + replace(txt, ';', '"/><row txt="') + '"/>') xm
	from @t) a
	CROSS APPLY xm.nodes('/row') as T2(Loc) 
26 фев 19, 13:19    [21819641]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
BredSpit
Member

Откуда:
Сообщений: 19
Необходима помощь в разборе строки
26 фев 19, 13:25    [21819650]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
MAULER
Member

Откуда:
Сообщений: 864
WarAnt,

Очень интересный пример! Но Вы используйте объявление табличной переменной. Правильно ли я понимаю, что въюху мне уже не сделать?
26 фев 19, 13:33    [21819658]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
MAULER,

автор
Но Вы используйте объявление табличной переменной.

но у него нет вашей таблицы
26 фев 19, 13:36    [21819662]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
MAULER
Member

Откуда:
Сообщений: 864
TaPaK,

всё, туплю))
26 фев 19, 13:45    [21819680]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
MAULER
Member

Откуда:
Сообщений: 864
WarAnt,

Благодарю! Такие выкрутасы вижу в первый раз!
26 фев 19, 14:05    [21819716]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
Функция, которая делит строку на слова
26 фев 19, 15:27    [21819851]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
WarAnt
MAULER,

declare @t table (id int, txt varchar(1000))

insert @t SELECT 1, '2019-02-01;2019-02-05;2019-02-11'

SELECT id,T2.Loc.query('.').value('(/row/@txt)[1]' , 'varchar(10)')  
from
(
	SELECT id, convert(xml, '<row txt="' + replace(txt, ';', '"/><row txt="') + '"/>') xm
	from @t) a
	CROSS APPLY xm.nodes('/row') as T2(Loc) 



а чего не
declare @t table (id int, txt varchar(1000))

insert @t SELECT 1, '2019-02-01;2019-02-05;2019-02-11'

SELECT t.id, ss.value
FROM	   @t AS t
CROSS APPLY String_split(t.txt, ';') ss
26 фев 19, 16:49    [21819932]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
MAULER
Member

Откуда:
Сообщений: 864
Dzianis,

Могу ошибаться, но скорее всего, Ваш вариант требователен к уровню совместимости БД (не ниже 130).
26 фев 19, 19:47    [21820042]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
MAULER
Dzianis,

Могу ошибаться, но скорее всего, Ваш вариант требователен к уровню совместимости БД (не ниже 130).


все верно.
Но вы же не указали под какую версию интересуетесь.
вариант через nodes то же требует уровень совместимости не ниже 100
27 фев 19, 16:20    [21820832]     Ответить | Цитировать Сообщить модератору
 Re: Как значения в строке разделённые ";" превратить в столбец?  [new]
Сруль.
Member

Откуда:
Сообщений: 119
Спасибо Вам, порадовали старика.
Забодался в Ютубе торчать.
Резальтик, как у вас, только сортировочка по датам у вас
слегка нарушена, а так те же 11 строк.
это подготовка данных

if object_id('t_sql_ru') is not null
drop table [t_sql_ru]
go

CREATE TABLE [dbo].[t_sql_ru](
	[login1] [varchar](50) NULL,
	[col1] [varchar](50) NULL,
	[col2] [varchar](50) NULL,
	id int identity
) ON [PRIMARY]

GO

insert into t_sql_ru
select 'DOMAIN\ivanov','2019-02-01;2019-02-05;2019-02-11','2019-02-22;2019-02-24'


insert into t_sql_ru
select 'DOMAIN\petrov','2019-03-10;2019-01-15','2019-02-12;2019-07-22;2019-05-22;2019-02-02'


select * from [t_sql_ru]

это процедура-развал на то что промеж ;
/* Всякая вспомогательная муть один разик придётся прогнать 
create table t_logins(login1 varchar(50),col varchar(50),indicator varchar(50))
*/
create  proc [dbo].[p_tochka_zapytay](@login varchar(50), @col varchar(50), @indicator int)
as
begin
if charindex(';',@col)=0 
begin
insert into t_logins
select @login,@col,@indicator
return
end

insert into t_logins
select @login,left(@col,charindex(';',@col)-1),@indicator

set @col=right(@col,len(@col) - charindex(';',@col))

exec [p_tochka_zapytay] @login,@col,@indicator
return
end
go

это уже выдача результата.
------------------------------------------------------------------
--delete t_logins
declare @index int, @max int
declare @login varchar(50),@col varchar(50) 

select @index=1 ,@max=(select max(id) from t_sql_ru)

while(@index<=@max)
begin

select @login=login1,@col=col1 from [t_sql_ru] where [id]=@index
execute dbo.p_tochka_zapytay @login,@col,1

select @login=login1,@col=col2 from [t_sql_ru] where [id]=@index
execute dbo.p_tochka_zapytay @login,@col,0

set @index=@index+1
end
select distinct
login1,
col col,
case when indicator=1 then 1 else 0 end [move],
case when indicator=1 then 0 else 1 end [add]
from t_logins
order by login1,col


К сообщению приложен файл. Размер - 17Kb
27 фев 19, 18:54    [21821011]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить