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

Откуда: Москва
Сообщений: 546
Есть таблица с двумя столбцами: телефоны (уникальные записи) и ФИО (могут повторяться).
Для выгрузки в одно капризное ПО надо сделать вьюху, в которой каждому ФИО будет соответствовать все его телефоны (в одном поле через запятую).
select distinct NAME,  

(SELECT STUFF(
 ( SELECT ', ' + PHONE_NUMBER 
	FROM dbo.MOBILE_PHONE_LIST 

	FOR XML PATH ('')),1,1,'')) as Phone
	
	from dbo.MOBILE_PHONE_LIST
Но тут для каждого ФИО выводится одинаковый список из всех имеющихся телефонов, что нехорошо.
Еще был избыточный вариант с проверкой значения по второй таблице:
SELECT
    G.CONTACT_NAME,
    stuff(
    (
    select cast(',' as varchar(max)) + U.PHONE_NUMBER
    from dbo.MOBILE_PHONE_LIST U
    WHERE U.NAME = G.CONTACT_NAME
    order by U.NAME
    for xml path('')
    ), 1, 1, '') AS USERS
FROM
    dbo.USERS G
ORDER BY
    G.CONTACT_NAME ASC;
который просто выводит ошибку "Invalid length parameter passed to the RIGHT function."

Подскажите пожалуйста способ решения!
21 июн 11, 18:17    [10849659]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
Baddy
Member

Откуда: Харьков
Сообщений: 174
автор
Но тут для каждого ФИО выводится одинаковый список из всех имеющихся телефонов, что нехорошо.


ну так а где же ваш GROUP BY?
21 июн 11, 18:32    [10849761]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
Netmould
Member

Откуда: Москва
Сообщений: 546
Подскажите, куда его? Если добавлять к верхнему селекту, то ничего в общем не меняется.
21 июн 11, 18:41    [10849816]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
Baddy
Member

Откуда: Харьков
Сообщений: 174
а как же фак?
https://www.sql.ru/faq/faq_topic.aspx?fid=731
21 июн 11, 18:49    [10849880]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
Netmould
Member

Откуда: Москва
Сообщений: 546
Жалко, что раньше не посмотрел :).
Влюбом случае, нагуглил еще одно решение (работающее) - мб кому и пригодится.
WITH    q (id, prodname) AS
        (
        SELECT * FROM dbo.MOBILE_PHONE_LIST
        ),
        qs(id, prodname, rn, cnt) AS
        (
        SELECT  id, prodname,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY prodname),
                COUNT(*) OVER (PARTITION BY id)
        FROM    q
        ),
        t (id, prodname, gc, rn, cnt) AS
        (
        SELECT  id, prodname,
                CAST(prodname AS NVARCHAR(MAX)), rn, cnt
        FROM    qs
        WHERE   rn = 1
        UNION ALL
        SELECT  qs.id, qs.prodname,
                CAST(t.gc + ', ' + qs.prodname AS NVARCHAR(MAX)),
                qs.rn, qs.cnt
        FROM    t
        JOIN    qs
        ON      qs.id = t.id
                AND qs.rn = t.rn + 1
        )
SELECT  id, gc
FROM    t
WHERE   rn = cnt
OPTION (MAXRECURSION 0)
21 июн 11, 18:55    [10849919]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
Netmould
Member

Откуда: Москва
Сообщений: 546
Попробовал то что в факе написано (уж очень медленно мой вариант работает), в случае с нетривиальными данными все примеры из фака вываливаются с одинаковой ошибкой - Invalid length parameter passed to the RIGHT function.
22 июн 11, 18:26    [10857275]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
step_ks
Member

Откуда:
Сообщений: 936
в факе по обсуждаемой теме вроде нет функции right
22 июн 11, 22:44    [10858273]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
Netmould
Member

Откуда: Москва
Сообщений: 546
step_ks,

Я знаю :). У меня тоже нет. Тем не менее, вываливается такая ошибка.
23 июн 11, 10:28    [10859487]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
step_ks
Member

Откуда:
Сообщений: 936
запрос-то не хотите показать?
23 июн 11, 10:30    [10859501]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
Vaja
Member

Откуда:
Сообщений: 49
автор
SELECT name, (
SELECT a.PHONE_NUMBER +N';' as 'data()'
FROM dbo.MOBILE_PHONE_LIST AS a
WHERE b.name = a.name
for xml path('')
) as PHONE_NUMBERS
from dbo.MOBILE_PHONE_LIST b
group by name
23 июн 11, 10:45    [10859604]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
step_ks
Member

Откуда:
Сообщений: 936
кто-то чего-то не договаривает.
dbo.MOBILE_PHONE_LIST - эт не представление ли?
Покажите план запроса.
23 июн 11, 11:08    [10859800]     Ответить | Цитировать Сообщить модератору
 Re: GROUP_CONCAT для MS SQL?  [new]
Netmould
Member

Откуда: Москва
Сообщений: 546
Да, вьюха.
SELECT name, (
SELECT a.PHONE_NUMBER +N';' as 'data()'
FROM dbo.MOBILE_PHONE_LIST AS a 
WHERE b.name = a.name 
for xml path('') 
) as PHONE_NUMBERS
from dbo.MOBILE_PHONE_LIST b
group by name
- работает (я так понимаю, что у меня в примере выше был косяк с форматом данных), но заметно медленней, чем то что я привел выше (таблица ~1300 записей, ~200 из них группируются - запрос через for xml path отработал за 2:15, запрос через WITH - за 1:05).
23 июн 11, 14:31    [10861981]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить