Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
Доброго времени суток, форумчане.
Видел уже множество тем на этом форуме по объединению строк, с помощью group_by, xml path, string_agg, но так и не получилось выродить у меня решение моей проблемы.
Прошу прощения, но к сожалению пришлось-таки создать топик.
К сути:
Есть огромное множество строк в таблице, среди них 4 (допустим) строки принадлежат одному человеку. То есть, в одной строке имеется ФИО, в другой строке имеется почта, в третьей строке имеется телефон, в четвертой-клиент айди (клиент айди имеется у всех четырех строк). При этом, те значения, которых нет в какой-либо строке это NULL.
Нужно все четыре строки объединить в одну, при этом клиент айди сделать такой, который был в самой первой строке.

И так во всей таблице.
Например как в скриншоте. (который я не вижу чтобы загрузился)
Версия sql 17

Сообщение было отредактировано: 10 ноя 20, 16:34
10 ноя 20, 16:34    [22229666]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Oleg_SQL
Member

Откуда: от туда
Сообщений: 50
Nevillested, а где скриншот?

Ну по всей логике сначала нужно построить таблицу со всеми возможными столбцами, а затем уже заполнить ее.
Таблица может быть временной ))
10 ноя 20, 16:40    [22229669]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
Oleg_SQL,
Скриншот не получается по каким-то причинам прикрепить.Тогда приведу пример в текстовом виде:
Имеется такое:

client_id | name | phone | e-mail 
1         | Иван |       | i@iv.ru
2         |      | 99999 |
3         | Иван |       | i@iv.ru
4         |      | 99999 | i@iv.ru


Необходимо получить:

client_id | name | phone | e-mail 
1         | Иван | 99999 | i@iv.ru


Сообщение было отредактировано: 10 ноя 20, 16:42
10 ноя 20, 16:47    [22229673]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
Nevillested,

Например как в скриншоте. (который я не вижу чтобы загрузился)


на нет и суда нет.
ваше описание задачи слишком размытое, прикладывайте тестовые данные и результат который хотите получить.

add: опередили третьим постом.

ну в таком случае как должен выглядеть результат вот с такими данными ?
client_id | name | phone | e-mail 
1         | Иван |       | i@iv.ru
2         |      | 99999 |
3         | Иван |       | i@iv.ru
4         |      | 99999 | i@iv.ru
5       | Андрей |       | i@iv.ru
7       |  Денис |       | 
8       |        | 8888  | b@iv.ru
9       |        | 9999  | c@iv.ru


Сообщение было отредактировано: 10 ноя 20, 16:50
10 ноя 20, 16:49    [22229675]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
msLex
Member

Откуда:
Сообщений: 8455
Nevillested
Oleg_SQL,
Скриншот не получается по каким-то причинам прикрепить.Тогда приведу пример в текстовом виде:
Имеется такое:

client_id | name | phone | e-mail 
1         | Иван |       | i@iv.ru
2         |      | 99999 |
3         | Иван |       | i@iv.ru
4         |      | 99999 | i@iv.ru



Необходимо получить:

client_id | name | phone | e-mail 
1         | Иван | 99999 | i@iv.ru


А как тоже самое будет выглядеть на нескольких клиентах?
Какие правила деления записей на отдельных клиентов?
10 ноя 20, 16:50    [22229676]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
felix_ff,
Сообщение выше не подойдет?
10 ноя 20, 16:51    [22229677]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
msLex,
Я думаю, что можно было бы это сделать по условию совпадения, если фио/дата рождения/почта совпадают, то это 1 клиент
Например, если есть три строки, в которых есть одна и та же почта-схлопнуть их в одну. Или же если почты нет, а есть одинаковый телефон-также объединить в одну строку.
Ну или ФИО+дата рождения (хотя даже в таком случае могут существовать несколько человек с такими же ФИО+дата рождения, поэтому такой вариант не подойдет скорее всего)

Сообщение было отредактировано: 10 ноя 20, 16:51
10 ноя 20, 16:54    [22229680]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
msLex
Member

Откуда:
Сообщений: 8455
Nevillested
msLex,
Я думаю, что можно было бы это сделать по условию совпадения, если фио/дата рождения/почта совпадают, то это 1 клиент
Например, если есть три строки, в которых есть одна и тоже почта-схлопнуть их в одну. Или же если почты нет, а если телефон-также объединить в одну строку.
Ну или ФИО+дата рождения (хотя даже в таком случае могут существовать несколько человек с такими же ФИО+дата рождения, поэтому такой вариант не подойдет скорее всего)


А если у одного ФИО окажется несколько телефонов или email-ов?
А если к этим телефонам или email-ам привязаны другие телефоны, email-ы, ФИО?
10 ноя 20, 16:57    [22229682]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
Nevillested,

приведите структуру таблицы и тестовые данные.

вы говорите что есть ФИО и дата рождения а приводите в виде тестовых данных какое то подобие где есть только name

идентификатор client_id у вас нифига не идентификатор, а какой то сквозной нумератор.
я пока не понимаю структуру данных хранящихся у вас в таблице.

такое впечатление что там набор строк вида
create table myTable (
[data] varchar(max)
);

insert into myTable ('1         | Иван |       | i@iv.ru');
insert into myTable ('2         | Иван |       | i@iv.ru');
insert into myTable ('3         | Иван |       | i@iv.ru'); 
blablabla
10 ноя 20, 16:59    [22229683]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
msLex,
В таком случае, у этого человека будет несколько клиент_айди и это будет правильно
10 ноя 20, 17:00    [22229684]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
felix_ff,
Таблица состоит из столбцов: client_id, phone, surname, firstname, sex, date_birthday, age, city, e-mail, timezone, sum_amount
Я привел выше пример для простоты, чтобы не вдаваться в подробности и понять логику, как это сделать, но, видимо, нужно все
10 ноя 20, 17:06    [22229687]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
msLex
Member

Откуда:
Сообщений: 8455
Nevillested
msLex,
В таком случае, у этого человека будет несколько клиент_айди и это будет правильно


Не сомневаюсь, что это правильно, но не понимаю, как в вашей задачи это разделение происходит.

Например


NAME1 PNONE1 NULL
NAME2 PNONE2 NULL
NAME3 NULL EMAIL1
NAME4 NULL EMAIL1
NULL PHONE1 EMAIL1
NULL PHONE2 EMAIL1


Сколько это клиентов и где чьи телефоны и email?
10 ноя 20, 17:06    [22229688]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
msLex,
Ну в Вашем примере не совсем верно, такого примера просто не может быть, т.к. EMAIL1 не может быть одновременно сразу у NAME1, NAME2, NAME3, NAME4
Правильный пример такой:

NAME1 PNONE1 NULL
NAME2 PNONE2 NULL
NAME3 NULL EMAIL3
NAME4 NULL EMAIL4
NULL PHONE1 EMAIL1
NULL PHONE2 EMAIL2

Результат:

NAME1 PNONE1 EMAIL1
NAME2 PHONE2 EMAIL2
NAME3 NULL EMAIL3
NAME4 NULL EMAIL4


В конечном итоге, нужно чтобы эти строки объединялись по выполняемым условиям. Т.е. имеется минимальный набор условий, при которых строки объединяться, например в двух строках совпадают 4 столбца, как минимум. Например это surname, firstname, sex, date_birthday

Сообщение было отредактировано: 10 ноя 20, 17:10
10 ноя 20, 17:12    [22229691]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
Nevillested
felix_ff,
Таблица состоит из столбцов: client_id, phone, surname, firstname, sex, date_birthday, age, city, e-mail, timezone, sum_amount
Я привел выше пример для простоты, чтобы не вдаваться в подробности и понять логику, как это сделать, но, видимо, нужно все


я образно понимаю чего вы хотите добиться, но не до конца понятна логика отделения данных одного человека от другого

idnamephonemail
1Вася
21111@iv.ru
3Петя222
43332@iv.ru
5Коля
6Миша3@iv.ru
74@iv.ru
8777
9Юра9994@iv.ru
10888


idnamephonemail
1Вася1111@iv.ru
3Петя222;3332@iv.ru
5Коля
6Миша7773@iv.ru;4@iv.ru
9Юра888;9994@iv.ru


вы вот такого хотите добиться?
10 ноя 20, 17:17    [22229692]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
msLex
Member

Откуда:
Сообщений: 8455
Nevillested
msLex,
Ну в Вашем примере не совсем верно


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

Если это не какой-нибудь тестовый набор данных, генерируемый на основе изначально нормализованных данных, а данные вносимые пользователями, то подобные коллизии неизбежны. А значит вам нужно придумать некую стратегию по их корректной интерпретации.
10 ноя 20, 17:20    [22229695]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
Nevillested
msLex,
Ну в Вашем примере не совсем верно, такого примера просто не может быть, т.к. EMAIL1 не может быть одновременно сразу у NAME1, NAME2, NAME3, NAME4
Правильный пример такой:

NAME1 PNONE1 NULL
NAME2 PNONE2 NULL
NAME3 NULL EMAIL3
NAME4 NULL EMAIL4
NULL PHONE1 EMAIL1
NULL PHONE2 EMAIL2

Результат:

NAME1 PNONE1 EMAIL1
NAME2 PHONE2 EMAIL2
NAME3 NULL EMAIL3
NAME4 NULL EMAIL4


В конечном итоге, нужно чтобы эти строки объединялись по выполняемым условиям. Т.е. имеется минимальный набор условий, при которых строки объединяться, например в двух строках совпадают 4 столбца, как минимум. Например это surname, firstname, sex, date_birthday



declare @t table (
       [client_id] int,
       [name] varchar(255),
       [phone] varchar(50),
       [mail] varchar(50)
);

insert into @t values (1, 'NAME1', 'PHONE1', NULL);
insert into @t values (2, 'NAME2', 'PHONE2', NULL);
insert into @t values (3, 'NAME3', NULL,     'EMAIL3');
insert into @t values (4, 'NAME4', NULL,     'EMAIL4');
insert into @t values (5, NULL,    'PHONE1', 'EMAIL1');
insert into @t values (6, NULL,    'PHONE2', 'EMAIL2');
insert into @t values (7, 'NAME5',    'PHONE3', NULL);
insert into @t values (8, NULL,    'PHONE3', 'EMAIL5');


with clients as (
    select [client_id], [name], [phone], [mail] from @t where [name] is not null
)
select
      clients.[client_id],
      clients.[name],
      coalesce(clients.[phone], c1.[phone], c2.[phone]) as [phone],
      coalesce(clients.[mail], c1.[mail], c2.[phone]) as [mail]
from clients
    outer apply (select top(1) [client_id], [phone], [mail] from @t t where t.[client_id] > clients.[client_id] and t.[phone] = clients.[phone]) c1
    outer apply (select top(1) [client_id], [phone], [mail] from @t t where t.[client_id] > clients.[client_id] and t.[mail] = clients.[mail]) c2


и так добавляете еще кучу связей по которым могут быть совпадения

Сообщение было отредактировано: 10 ноя 20, 17:31
10 ноя 20, 17:33    [22229706]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
felix_ff, немного не так.
Простите меня, за мои тщетные попытки объяснить, но я попробую снова, но уже детально.
Имеется:
client_id | firstname | surname | sex | date_birthday | age | city   |  e-mail   | timezone | sum_amount | phone
1         | Иван      | Иванов  |  м  |    01.01.72   | 48  | Moscow |           |          |            |      
2         | Ольга     |         |  ж  |    02.03.90   |     |        | ola@o.ru  |    +3    |    5000    | 22222
3         | Иван      |         |     |    01.01.72   |     |        | ivan@i.ru |    +3    |    6000    | 11111
4         | Денис     |         |  м  |    03.03.80   |     |        | den@i.ru  |          |    4000    | 33333
5         | Андрей    |         |  м  |    01.01.72   | 48  |        |           |          |            |      
7         | Денис     |         |     |               |     |        |           |    +5    |    4000    | 33333
8         | Ольга     | Олевна  |  ж  |    02.03.90   | 30  | Moscow |           |          |    5000    | 22222
9         | Иван      | Иванов  |  м  |               |     |        |           |          |    6000    | 11111

Схлопывание дублей происходит конкретно, если в двух и более строках одинаковы phone и sum_amount. В противном случае-если соответствий у одной строки с другими по phone и sum_amount не найдено, то ее не трогать и это не считается дублем.

Результат:
client_id | firstname | surname | sex | date_birthday | age | city   |  e-mail   | timezone | sum_amount | phone
1         | Иван      | Иванов  |  м  |    01.01.72   | 48  | Moscow |           |          |            |      
2         | Ольга     | Олевна  |  ж  |    02.03.90   | 30  | Moscow | ola@o.ru  |    +3    |    5000    | 22222
3         | Иван      | Иванов  |  м  |    01.01.72   |     |        | ivan@i.ru |    +3    |    6000    | 11111
4         | Денис     |         |  м  |    03.03.80   |     |        | den@i.ru  |    +5    |    4000    | 33333
5         | Андрей    |         |  м  |    01.01.72   | 48  |        |           |          |            |      


Сообщение было отредактировано: 10 ноя 20, 17:59
10 ноя 20, 17:59    [22229722]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
msLex,
Нет, эти данные не вносятся пользователями, они произрастают из другой черновой таблицы, которая проходит "очистку" определнными процессами.
10 ноя 20, 18:01    [22229726]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
felix_ff,
Большое спасибо, пока что мало что понятно, но буду сейчас пытаться разобрать Ваш код)

Сообщение было отредактировано: 10 ноя 20, 17:58
10 ноя 20, 18:03    [22229728]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
godsql
Member

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

Тогда сделайте временную таблицу с уникальными phone и sum_amount и на основе ее, заполняйте остальные поля
10 ноя 20, 18:13    [22229740]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
Nevillested,

declare @t table (
       [client_id] int,
       [name] varchar(255),
       [surname] varchar(255),
       [sex] char,
       [birthday] date,
       [phone] int,
       [amount] int
);

insert into @t values (1, 'Иван',   'Иванов', 'м',  '01.01.72',  null,  null)      
insert into @t values (2, 'Ольга',  null,     'ж',  '02.03.90',  22222, 500)
insert into @t values (3, 'Иван',   null,     null, '01.01.72',  11111, 600)
insert into @t values (4, 'Денис',  null,     'м',  '03.03.80',  33333, 400)
insert into @t values (5, 'Андрей', null,     'м',  '01.01.72',  null,  null)
insert into @t values (7, 'Денис',  null,     null, null,        33333, 400)
insert into @t values (8, 'Ольга',  'Олевна', 'ж',  '02.03.90',  22222, 500)
insert into @t values (9, 'Иван',   'Иванов', 'м',  null,        11111, 600);

with clients as (
    select
          count(1) over (partition by isnull([phone], cast(newid() as varbinary(6))), [amount] order by [client_id] range between unbounded preceding and unbounded following) as [xcnt], 
          sum(1) over (partition by isnull([phone], cast(newid() as varbinary(6))), [amount] order by [client_id]) as [xrn],
          *
    from @t
)
select [client_id], [name], [surname], [sex], [birthday], [phone], [amount] from clients where [xcnt] = 1
union
select
      c1.[client_id],
      coalesce(c1.[name], cx.[name]) as [name],
      coalesce(c1.[surname], cx.[surname]) as [surname],
      coalesce(c1.[sex], cx.[sex]) as [sex],
      coalesce(c1.[birthday], cx.[birthday]) as [birthday],
      c1.[phone],
      c1.[amount]
from clients c1
    outer apply (
         select
               max(c2.[name]) as [name],
               max(c2.[surname]) as [surname],
               max(c2.[sex]) as [sex],
               max(c2.[birthday]) as [birthday]
         from clients c2  
         where c2.[phone] = c1.[phone]
           and c2.[amount] = c1.[amount]
           and c2.[xcnt] > 1
           and c2.[xrn] > 1
    ) cx
where c1.[xcnt] > 1
  and c1.[xrn] = 1
order by client_id
10 ноя 20, 18:41    [22229752]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
godsql
Member

Откуда:
Сообщений: 134
тоже работает, но если четко соблюдается наличие уникальных пар phone+amount :)

использовал таблицу от felix_ff
with clients as (
    select distinct phone, amount
    from @t
	where phone is not null or  amount is not null
)
select  t3.[client_id],  t3.[name],  t3.[surname],  t3.[sex],  t3.[birthday], t1.[phone], t1.[amount] from clients t1
cross apply (
select min(t2.[client_id]) as [client_id],  max(t2.[name]) as [name],  max(t2.[surname]) as surname,
  max(t2.[sex]) as sex,  max(t2.[birthday]) as [birthday] from @t t2 
where t2.[phone]=t1.[phone] and t2.[amount]=t1.[amount]
) as t3


Сообщение было отредактировано: 10 ноя 20, 19:38
10 ноя 20, 19:42    [22229779]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
felix_ff,
Прошу прощения, может вопрос глупый, но я так понимаю, Вы создаете таблицу и вносите туда значения:

автор
declare @t table (
       [client_id] int,
       [name] varchar(255),
       [surname] varchar(255),
       [sex] char,
       [birthday] date,
       [phone] int,
       [amount] int
);

insert into @t values (1, 'Иван',   'Иванов', 'м',  '01.01.72',  null,  null)      
insert into @t values (2, 'Ольга',  null,     'ж',  '02.03.90',  22222, 500)
insert into @t values (3, 'Иван',   null,     null, '01.01.72',  11111, 600)
insert into @t values (4, 'Денис',  null,     'м',  '03.03.80',  33333, 400)
insert into @t values (5, 'Андрей', null,     'м',  '01.01.72',  null,  null)
insert into @t values (7, 'Денис',  null,     null, null,        33333, 400)
insert into @t values (8, 'Ольга',  'Олевна', 'ж',  '02.03.90',  22222, 500)
insert into @t values (9, 'Иван',   'Иванов', 'м',  null,        11111, 600);



Я так понимаю, в моём случае, раз у меня уже есть таблица с моими значениями, мне этого делать не надо?
12 ноя 20, 14:57    [22230792]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
Nevillested,

правильно понимаете, я просто привел пример тестовых данных как оно в принципе должно выглядеть, а вам уже нужно самому модифицировать запрос на использование не @t таблицы, а вашей реальной
12 ноя 20, 15:04    [22230803]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
godsql
Member

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

вообще-то, ваша задача, судя по приведенному примеру, описанию и результату от 10 ноя 20, 17:59
достаточно просто раскладывается на :
1) простой select с группировкой phone и sum_amount, где phone и sum_amount "не равны null" (или "не пустые")
что- то типа
select min(client_id), max(firstname), max(surname), max(sex), max(date_birthday), max( age) , max(city ), max(e-mail) , max(timezone),sum_amount , phone from Table
where sum_amount is not null and  phone  is not null   -- или что там еще означает, что поле пустое
group by sum_amount , phone 


2) плюс, используя union all, собираем все оставшиеся строки, которые не схлопнулись по sum_amount , phone обычным select -ом
union all
select client_id , firstname , surname , sex , date_birthday , age , city   ,  e-mail   , timezone , sum_amount , phone from Table
where phone is  null or sum_amount  is null 
12 ноя 20, 18:52    [22231048]     Ответить | Цитировать Сообщить модератору
 Re: Объединение строк  [new]
Nevillested
Member

Откуда: Москва
Сообщений: 12
felix_ff, понял-принял, попробую подкрутить под свою таблицу.
godsql, спасибо за идею, звучит вроде правильно и адекватно, но что-то смущает, не могу понять что (может простота варианта?)
13 ноя 20, 10:52    [22231278]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить