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

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

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

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

SQL

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

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

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

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

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

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

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

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 символов так и останутся пробелами

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

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

@Landser 57 / 7 / 4 Регистрация: 18.04.2009 Сообщений: 429
	26.08.2010, 22:32 [ТС]	12
	и сколько не прибавляй к 60 пробелам - первые 60 символов так и останутся пробелами видимо в этом и дело спасибо большое)) 0

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

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

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