Аналог функции GROUP_CONCAT в MS SQL Server

@Landser · Регистрация: 18.04.2009

Студворк — интернет-сервис помощи студентам

доброго времени суток!вот какой вопросик имеется:
есть таблица, поля фамилия, город
name | city
-------------------
Фролов |Москва
Иванов |Саратов
Сергеев|Пенза
Гришин |Саратов
--------------------
Андреев|Москва
есть надобность с помощью запроса получить из неё данные такого вида:
name | city
---------------------------
Москва | Андреев Фролов
Саратов | Гришин Иванов
для случая MySql мне тут подсказали, что существует мегаполезная на самом деле функция GROUP_CONCAT, которая делает как раз то, что мне нужно:

SQL
1
2
3
4
SELECT `city`, GROUP_CONCAT(`name` ORDER BY `name` SEPARATOR ' ') AS `names` 
FROM `table1`
GROUP BY `city`
ORDER BY `city`

а есть ли в MS SQL Server аналог этой функции,или что либо подобное, что может выводить данные таким вот образом?кто нибудь делал такую выборку в MS Sql?

@iap · 23.08.2010, 10:00

Version>=90
Простой вариант:

SQL
1
2
3
4
5
SELECT city,
STUFF((SELECT ' '+[name] FROM table1 TT WHERE TT.city=T.city FOR XML PATH('')),1,1,'') names
FROM table1 T
GROUP BY city
ORDER BY city;

Если в строках встречаются спецсимволы XML (">","<" и т.п.), то

SQL
1
2
3
4
5
SELECT city,
STUFF((SELECT ' '+[name] FROM table1 TT WHERE TT.city=T.city FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'') names
FROM table1 T
GROUP BY city
ORDER BY city;

Кроме того, можно воспользоваться рекурсивным CTE

@Landser · 23.08.2010, 19:13 **[ТС]**

iap, спасибо большое за ответ! но только вот забыл сказать - у меня 8.0- 2000 й сервер- и никак не перейти выше..

в нём как то можно?

@new_in_net · 23.08.2010, 21:35

Можно вот так, с помошью курсора по городам:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE @City VARCHAR(50), @People VARCHAR(MAX)
SELECT City, People INTO #Table1 FROM Table1 GROUP BY City
 
DECLARE ToProcess_Cursor CURSOR FOR 
    SELECT City FROM Table1
OPEN ToProcess_Cursor
FETCH NEXT FROM ToProcess_Cursor INTO @City
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @People = '';
    SELECT @People = ISNULL(@People, '') + People FROM Table1 WHERE City = @City
    UPDATE #Table1
        SET People = @People
        WHERE City = @City
    FETCH NEXT FROM ToProcess_Cursor INTO @City
END
CLOSE ToProcess_Cursor
DEALLOCATE ToProcess_Cursor
 
SELECT * FROM #Table1
DROP TABLE #Table1

@iap · 23.08.2010, 21:44

Сложение символьных полей в запросе

@Landser · 24.08.2010, 20:16 **[ТС]**

new_in_net , iap спасибо за ответы

приду сегодня на комп, где база, попробую

Добавлено через 13 часов 7 минут
iap, код для sql 2000 отлично работает на тестовой базе, спасибо,но я не имею полномочий на создание функций и процедур....

new_in_net, пробовал Ваш код,выдаёт такую ошибку в строке

SQL
1
SELECT City, People INTO #Table1 FROM Table1 GROUP BY City

table1.People' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause....

может я что то не так сделал?

@new_in_net · 24.08.2010, 23:47

Сообщение от Landser

.............
new_in_net, пробовал Ваш код,выдаёт такую ошибку в строке

SQL
1
SELECT City, People INTO #Table1 FROM Table1 GROUP BY City

table1.People' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause....

может я что то не так сделал?

Неее я недосмотрел... поправь на:

T-SQL
1
SELECT City, VARCHAR(MAX) as People INTO #Table1 FROM Table1 GROUP BY City

@Landser · 25.08.2010, 20:23 **[ТС]**

new_in_net, исправил, как Вы сказали, в результате выводятся таблица с полями City, People, в City - города(уникальные, без повторений), а в People пусто почему то...не могу понять никак, вроде разобрал как код работает, а в People пусто..
использую такой код

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE @City CHAR(50), @People CHAR(60)
 
SELECT City, CHAR(50) AS People INTO #Test_18_08 FROM Test_18_08 GROUP BY City
DECLARE ToProcess_Cursor CURSOR FOR 
        SELECT City FROM Test_18_08
OPEN ToProcess_Cursor
FETCH NEXT FROM ToProcess_Cursor INTO @City
WHILE @@FETCH_STATUS = 0
BEGIN
        SET @People = '';
        SELECT @People = ISNULL(@People, '') + People FROM Test_18_08 WHERE City = @City
        UPDATE #Test_18_08
                SET People = @People
                WHERE City = @City
        FETCH NEXT FROM ToProcess_Cursor INTO @City
END
CLOSE ToProcess_Cursor
DEALLOCATE ToProcess_Cursor
 
SELECT * FROM #Test_18_08
DROP TABLE #Test_18_08

@new_in_net · 25.08.2010, 23:11

Если этот стайтмент возвращает города и людей:

T-SQL
1
SELECT * FROM Test_18_08 ORDER BY City

То данный рабочий код (мне пришлось его слегка подправить) возвращает

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE @City CHAR(50), @People VARCHAR(MAX)
 
SELECT City, Cast('' AS VARCHAR(MAX)) AS People INTO #Test_18_08 FROM Test_18_08 GROUP BY City
DECLARE ToProcess_Cursor CURSOR FOR 
        SELECT City FROM #Test_18_08
OPEN ToProcess_Cursor
FETCH NEXT FROM ToProcess_Cursor INTO @City
WHILE @@FETCH_STATUS = 0
BEGIN
        SET @People = '';
        SELECT @People = ISNULL(@People, '') + Person + ','
            FROM Test_18_08 WHERE City = @City
        UPDATE #Test_18_08
                SET People = @People
                WHERE City = @City
        FETCH NEXT FROM ToProcess_Cursor INTO @City
END
CLOSE ToProcess_Cursor
DEALLOCATE ToProcess_Cursor
 
SELECT * FROM #Test_18_08 ORDER BY City
DROP TABLE #Test_18_08

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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
City                                                 Person
----------------------------------------------------------- 
 Buenos Aires                                        Pablo
 Rijswijk (ZH)                                       Ellen
 Rousset                                             Yves
 Zug                                                 Stefan
 
 
 
City                                                 People
---------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Buenos Aires                                        Pablo,
 Rijswijk (ZH)                                       Ellen,
 Rousset                                             Yves,
 Zug                                                 Stefan,
Aachen                                               Bernd,
AB Amsterdam                                         Petra,
Abu Dhabi                                            Rana,M. Anwer,Hamza,Georges,Hussain,Salem,Khaled,Samar,Omer,Maheen,Nada,Fenar,Abdul Salam,Ashok,Anita,Obaid,Abdulla,Saeed,Ahmed,Shahid,Ciaran,Paul,Alexandra,Fatima,Abbas,Marwah Ibrahim,Gopalakrishnan,Derek,Arun,Kallingapuram,Zafar,Hazem,Guy,Hareb,Jose Mari
AC Zeist                                             Gerrit,
Akron                                                Donald,Eileen,Charlene,R. Terry,Peter,Richard,

Данные - случайные.
Если MS SQL 2005 не поддерживает VARCHAR(MAX) - замени VARCHAR(на побольше)

@Landser · 26.08.2010, 21:21 **[ТС]**

new_in_net , спасибо большое!
но знаете что самое интересное: на одной машине этот код выдаёт по прежнему пустую колонку People как не странно(эта машина в сети, но я выполнял на локале всё,+ на ней работает через функцию, которую iap по ссылке предложил )

а на другой машине, не подсоединённой к сети всё прекрасно работает

никак не пойму только вот, что такого может быть, что на одном компе не работает, а на другом работает
СУБД вообще с одного дистрибутива ставил, антивирусы одинаковые.. Как по Вашему, что энта может быть??

Кстати этот код

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE @City CHAR(50), @People CHAR(60)
 
SELECT City, CHAR(50) AS People INTO #Test_18_08 FROM Test_18_08 GROUP BY City
DECLARE ToProcess_Cursor CURSOR FOR 
        SELECT City FROM Test_18_08
OPEN ToProcess_Cursor
FETCH NEXT FROM ToProcess_Cursor INTO @City
WHILE @@FETCH_STATUS = 0
BEGIN
        SET @People = '';
        SELECT @People = ISNULL(@People, '') + People FROM Test_18_08 WHERE City = @City
        UPDATE #Test_18_08
                SET People = @People
                WHERE City = @City
        FETCH NEXT FROM ToProcess_Cursor INTO @City
END
CLOSE ToProcess_Cursor
DEALLOCATE ToProcess_Cursor
 
SELECT * FROM #Test_18_08
DROP TABLE #Test_18_08

и на "хорошей" машине выводит пустую колонку People

@new_in_net · 26.08.2010, 21:35

Замените везде CHAR(XX) на VARCHAR(ХХ), и увеличьте длину в поле-приемщика многих значений.
В противном случае CHAR(60) - это 60 пробелов, а не пустая строка как в случае VARCHAR(любой длины) и сколько не прибавляй к 60 пробелам - первые 60 символов так и останутся пробелами

А две машины могут отличатся по настройкам как интерпретировть:
если по настройке CHAR = VARCHAR, то будет работать, нет - выдаст пустую строку

@Landser · 26.08.2010, 22:32 **[ТС]**

и сколько не прибавляй к 60 пробелам - первые 60 символов так и останутся пробелами

видимо в этом и дело

спасибо большое))

Новые блоги и статьи Все статьи Все блоги /
PhpStorm 2025.3: WSL Terminal всегда стартует в ~ and_y87 14.12.2025 PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .	Access VikBal 11.12.2025 Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.	Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .
Поиск "дружественных имён" СОМ портов Argus19 22.11.2025 Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .	Сколько Государство потратило денег на меня, обеспечивая инсулином. Programma_Boinc 20.11.2025 Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .	Ломающие изменения в C#.NStar Alpha Etyuhibosecyu 20.11.2025 Уже можно не только тестировать, но и пользоваться C#. NStar - писать оконные приложения, содержащие надписи, кнопки, текстовые поля и даже изображения, например, моя игра "Три в ряд" написана на этом. . .	Мысли в слух kumehtar 18.11.2025 Кстати, совсем недавно имел разговор на тему медитаций с людьми. И обнаружил, что они вообще не понимают что такое медитация и зачем она нужна. Самые базовые вещи. Для них это - когда просто люди. . .	Создание Single Page Application на фреймах krapotkin 16.11.2025 Статья исключительно для начинающих. Подходы оригинальностью не блещут. В век Веб все очень привыкли к дизайну Single-Page-Application . Быстренько разберем подход "на фреймах". Мы делаем одну. . .

@Landser 57 / 7 / 4 Регистрация: 18.04.2009 Сообщений: 429
	23.08.2010, 19:13 [ТС]
	iap, спасибо большое за ответ! но только вот забыл сказать - у меня 8.0- 2000 й сервер- и никак не перейти выше..в нём как то можно? 0

@iap 1116 / 761 / 183 Регистрация: 27.11.2009 Сообщений: 2,272
	23.08.2010, 21:44
	Сложение символьных полей в запросе 1

@new_in_net 283 / 262 / 32 Регистрация: 11.11.2009 Сообщений: 605
	26.08.2010, 21:35
	Замените везде CHAR(XX) на VARCHAR(ХХ), и увеличьте длину в поле-приемщика многих значений. В противном случае CHAR(60) - это 60 пробелов, а не пустая строка как в случае VARCHAR(любой длины) и сколько не прибавляй к 60 пробелам - первые 60 символов так и останутся пробелами А две машины могут отличатся по настройкам как интерпретировть: если по настройке CHAR = VARCHAR, то будет работать, нет - выдаст пустую строку 1