ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
14 сообщений из 14, страница 1 из 1
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000225
Charles Weyland
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Charles Weyland
Участник
SQL Server 2017 enterprise
Данные, приходящие из внешней системы имеют вид:

Код: sql
1.
2.
declare @names nvarchar(200) = 'Иван/Пётр/Алексей'
declare @families nvarchar(200) = 'Колесников/Дмитриев/Романин'


Необходимо получить таблицу:
Фамилия ИмяКолесников ИванДмитриев ПётрРоманин Алексей

 
Там в оригинале чуть сложнее:
Но в том виде, в каком сформулирован вопрос выше, решит мою проблему.

Из внешней системы приходит таблица:
Проект дата ... ... ... Ответственный (фамилия) Ответственный (имя) ...А ............ Колесников/Дмитриев/Романин Иван/Пётр/Алексей...Б ............ Колесников/Щеколдин Иван/Дмитрий...В ............ Щеколдин/Дмитриев/Романин Дмитрий/Пётр/Алексей...

Нужно разбить её на 3:
  • проекты ,
  • ответственные и
  • таблица многие-ко-многим .
И первый шаг - сформировать таблицу из ответственных.
Почему такая таблица через жопу? долго объяснять, но на внешнюю систему никак не повлиять.


Начал отталкиваться от решения:

Код: sql
1.
2.
select id = ROW_NUMBER() over( order by value ), t.value 
from string_split(R.Service, '/') t


Но невозможно сделать потом join по id, поскольку все значения перемешаны. А выделенную красным строку не убрать.

 
Для приведённого в предыдущем спойлере усложнения
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select distinct fam, name
from Проекты
	cross apply (
		select fam,
			name
		from
			(
				select id = ROW_NUMBER() over( order by value ), fam = t.value --меняет порядок и рушит корректность соответствия имён фамилиям
				from string_split(R.family, '/') t
			) families
			join
			(
				select id = ROW_NUMBER() over( order by value ), [name]=t.value --меняет порядок и рушит корректность соответствия имён фамилиям
				from string_split(R.[Name], '/') t
			) names
			on families.id = names.id
	) t
order by family, name

...
Рейтинг: 0 / 0
18.09.2020, 12:38
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000237
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Решения на основе string_split не гарантируют правильного результата, ибо https://docs.microsoft.com/ru-ru/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15 Выходные строки могут быть расположены в любом порядке. Порядок не обязательно совпадает с порядком подстрок во входной строке.
Пишите собственную функцию парсинга строки, гарантирующую результирующий порядок подстрок.
Примеров реализации масса, в том числе и на этом форуме.
Из вчерашнего - 22199220
...
Рейтинг: 0 / 0
18.09.2020, 13:00
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000238
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
string_split не возвращает порядковый номер элемента.

Можно использовать альтернативный реализации string_split (CLR, UDF)
Можно вычислять порядок вот так


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
declare @names nvarchar(200) = 'Иван/Пётр/Алексей'



select 
	t.value
	, rn = row_number() over(order by  charindex('/' + t.value + '/', '/' + @names + '/'))
from string_split(@names, '/') t
...
Рейтинг: 0 / 0
18.09.2020, 13:02
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000241
Фотография skyANA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skyANA
Участник
Charles Weyland
SQL Server 2017 enterprise
Данные, приходящие из внешней системы имеют вид

Каким образом данные приходят? Через линкед сервер, по почте, через API?
...
Рейтинг: 0 / 0
18.09.2020, 13:19
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000251
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
declare @t table (project varchar, F varchar(max), N varchar(max))

insert into @t values
('А','Колесников/Дмитриев/Романин','Иван/Пётр/Алексей'),
('Б','Колесников/Щеколдин','Иван/Дмитрий'),
('В','Щеколдин/Дмитриев/Романин','Дмитрий/Пётр/Алексей')

-- select * from @t

;with cte as (
	select 
		t.*
		,a.xF
		,b.xN
		,cnt	=a.xF.value('count(/f)', 'int')
	from @t t
	cross apply(select cast('<f>'+replace(t.F,'/','</f><f>')+'</f>' as xml) as xF) a
	cross apply(select cast('<n>'+replace(t.N,'/','</n><n>')+'</n>' as xml) as xN) b
)
,q as (
	select
		cte.project
		,cte.xF
		,cte.xN
		,cte.xF.value('(f[1]/text())[1]','varchar(50)') as F
		,cte.xN.value('(n[1]/text())[1]','varchar(50)') as N
		,next_lvl	=2 
		,cnt
	from cte

	union all

	select
		q.project
		,q.xF
		,q.xN
		,q.xF.value('(f[sql:column("next_lvl")]/text())[1]','varchar(50)') 
		,q.xN.value('(n[sql:column("next_lvl")]/text())[1]','varchar(50)')
		,next_lvl + 1
		,cnt
	from q 
	where next_lvl <= cnt )

select 
	project
	,F
	,N 
from q
order by project, next_lvl



projectFNАКолесниковИванАДмитриевПётрАРоманинАлексейБКолесниковИванБЩеколдинДмитрийВЩеколдинДмитрийВДмитриевПётрВРоманинАлексей
...
Рейтинг: 0 / 0
18.09.2020, 13:46
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000254
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
msLex
Можно вычислять порядок вот так
будет проблема, если в списке есть тёзки
Код: sql
1.
2.
3.
4.
5.
6.
declare @names nvarchar(200) = 'Иван/Пётр/Алексей/Иван'

select 
	t.value
	, rn = row_number() over(order by  charindex('/' + t.value + '/', '/' + @names + '/'))
from string_split(@names, '/') t


valuernИван1Иван2Пётр3Алексей4
...
Рейтинг: 0 / 0
18.09.2020, 13:51
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000304
Фотография Ennor Tiegael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ennor Tiegael
Участник
В 2017 можно через json:
Код: sql
1.
2.
3.
4.
5.
6.
declare @names nvarchar(200) = N'Иван/Пётр/Алексей',
	@families nvarchar(200) = N'Колесников/Дмитриев/Романин';

select *
from openjson('["' + replace(@names, '/', '","') + '"]') g
	full join openjson('["' + replace(@families, '/', '","') + '"]') f on f.[key] = g.[key];
...
Рейтинг: 0 / 0
18.09.2020, 15:37
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000608
Михаил Л
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Михаил Л
Гость
Код: sql
1.
2.
declare @names nvarchar(200) = 'Иван/Пётр/Алексей'
declare @families nvarchar(200) = 'Колесников/Дмитриев/Романин'


Добрый день! Я извиняюсь. Когда выполняю этот код, получаю в ответ - "Выполнение команд успешно завершено. ", но ничего другого не вижу. Я так понимаю что где то создается таблица, может временная, но где ее посмотреть?
Очень плохо знаю sql.
...
Рейтинг: 0 / 0
20.09.2020, 06:06
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000719
PizzaPizza
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PizzaPizza
Участник
declare создает переменную @names nvarchar(200) и задает её значение = 'Иван/Пётр/Алексей'
...
Рейтинг: 0 / 0
21.09.2020, 02:58
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000821
Агрох
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрох
Участник
Волшебство рекурсий:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
WITH recursive R0 AS (
  SELECT 'a' As Pack, CAST('Иванов/Семёнов/Петров/Васечкин' AS VARCHAR(1000)) AS Fam, CAST('Семён/Иван/Семён/Фрол' AS VARCHAR(1000)) AS nam FROM RDB$DATABASE
  UNION ALL
  SELECT 'б' As Pack, CAST('Карпов/Кирин' AS VARCHAR(1000)) AS Fam, CAST('Иван/Иван/Семён' AS VARCHAR(1000)) AS nam FROM RDB$DATABASE
), R0_1 AS (
  SELECT Pack,
    CASE SUBSTRING(Fam FROM CHAR_LENGTH(Fam))
      WHEN '/' THEN Fam
      ELSE Fam||'/'
    end AS Fam,
    CASE SUBSTRING(nam FROM CHAR_LENGTH(nam))
      WHEN '/' THEN nam
      ELSE nam||'/'
    end AS nam
  FROM R0
), R1 AS (
  SELECT Pack,
    substring(Fam FROM 1 FOR position('/' in Fam) - 1) AS F,
    substring(Fam FROM position('/' in Fam) + 1) AS Fam,
    substring(nam FROM 1 FOR position('/' in nam) - 1) AS N,
    substring(nam FROM position('/' in nam) + 1) AS nam
  FROM R0_1

  UNION all

  SELECT Pack,
    substring(Fam FROM 1 FOR position('/' in Fam) - 1) AS F,
    substring(Fam FROM position('/' in Fam) + 1) AS Fam,
    substring(nam FROM 1 FOR position('/' in nam) - 1) AS N,
    substring(nam FROM position('/' in nam) + 1) AS nam
  FROM R1
  WHERE position('/' in Fam) > 0
    AND position('/' in nam) > 0
)
SELECT
  Pack,
  F,
  N
FROM R1;
...
Рейтинг: 0 / 0
21.09.2020, 11:50
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000826
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
Модератор:
Агрох,

Уважаемый волшебник, а не хотите сначала научится не путать разделы форума?
...
Рейтинг: 0 / 0
21.09.2020, 11:57
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40000836
Агрох
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрох
Участник
Гавриленко Сергей Алексеевич
Модератор:
Агрох,

Уважаемый волшебник, а не хотите сначала научится не путать разделы форума?


Ок, учту.
...
Рейтинг: 0 / 0
21.09.2020, 12:14
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40001290
Charles Weyland
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Charles Weyland
Участник
Очень интересная ветка получилась.
спасибо!
...
Рейтинг: 0 / 0
22.09.2020, 13:59
| Ответить | Цитировать | Написать  
Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
    #40001869
Михаил Л
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Михаил Л
Гость
PizzaPizza, спасибо!
...
Рейтинг: 0 / 0
23.09.2020, 19:30
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как строку xxx/yyy/zzz превратить в столбец и пронумеровать? непросто
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40000608&tid=1685614]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 37ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 63ms
get tp. blocked users:
 2ms
others: 15ms
total:  166ms
созд. / загр.: 166ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]