Имитация функции MySQL group_concat в Microsoft SQL Server 2005?

Нет НАСТОЯЩЕГО простого способа сделать это. Тем не менее, есть много идей.

Лучшее, что я нашел:

SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.columns AS extern
CROSS APPLY
(
    SELECT column_name + ','
    FROM information_schema.columns AS intern
    WHERE extern.table_name = intern.table_name
    FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY table_name, column_names;

Или версия, которая работает правильно, если данные могут содержать символы, такие как <

WITH extern
     AS (SELECT DISTINCT table_name
         FROM   INFORMATION_SCHEMA.COLUMNS)
SELECT table_name,
       LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names
FROM   extern
       CROSS APPLY (SELECT column_name + ','
                    FROM   INFORMATION_SCHEMA.COLUMNS AS intern
                    WHERE  extern.table_name = intern.table_name
                    FOR XML PATH(''), TYPE) x (column_names)
       CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names) 

С помощью приведенного ниже кода вы должны установить PermissionLevel = External в свойствах вашего проекта перед развертыванием и изменить базу данных, чтобы доверять внешнему коду (обязательно прочитайте в другом месте о рисках безопасности и альтернативах [например, сертификатах]), запустив команду ALTER DATABASE database_name SET НАДЕЖДАЕТСЯ ".

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,
MaxByteSize=8000,
IsInvariantToDuplicates=true,
IsInvariantToNulls=true,
IsInvariantToOrder=true,
IsNullIfEmpty=true)]
    public struct CommaDelimit : IBinarySerialize
{


[Serializable]
 private class StringList : List<string>
 { }

 private StringList List;

 public void Init()
 {
  this.List = new StringList();
 }

 public void Accumulate(SqlString value)
 {
  if (!value.IsNull)
   this.Add(value.Value);
 }

 private void Add(string value)
 {
  if (!this.List.Contains(value))
   this.List.Add(value);
 }

 public void Merge(CommaDelimit group)
 {
  foreach (string s in group.List)
  {
   this.Add(s);
  }
 }

 void IBinarySerialize.Read(BinaryReader reader)
 {
    IFormatter formatter = new BinaryFormatter();
    this.List = (StringList)formatter.Deserialize(reader.BaseStream);
 }

 public SqlString Terminate()
 {
  if (this.List.Count == 0)
   return SqlString.Null;

  const string Separator = ", ";

  this.List.Sort();

  return new SqlString(String.Join(Separator, this.List.ToArray()));
 }

 void IBinarySerialize.Write(BinaryWriter writer)
 {
  IFormatter formatter = new BinaryFormatter();
  formatter.Serialize(writer.BaseStream, this.List);
 }
    }

Я проверил это с помощью запроса, который выглядит так:

SELECT 
 dbo.CommaDelimit(X.value) [delimited] 
FROM 
 (
  SELECT 'D' [value] 
  UNION ALL SELECT 'B' [value] 
  UNION ALL SELECT 'B' [value] -- intentional duplicate
  UNION ALL SELECT 'A' [value] 
  UNION ALL SELECT 'C' [value] 
 ) X 

И дает: A, B, C, D

Возможно, уже слишком поздно, чтобы приносить пользу, но разве это не самый простой способ сделать что-то?

SELECT     empName, projIDs = replace
                          ((SELECT Surname AS [data()]
                              FROM project_members
                              WHERE  empName = a.empName
                              ORDER BY empName FOR xml path('')), ' ', REQUIRED SEPERATOR)
FROM         project_members a
WHERE     empName IS NOT NULL
GROUP BY empName

Об ответе Дж. Хардимана, как насчет:

SELECT empName, projIDs=
  REPLACE(
    REPLACE(
      (SELECT REPLACE(projID, ' ', '-somebody-puts-microsoft-out-of-his-misery-please-') AS [data()] FROM project_members WHERE empName=a.empName FOR XML PATH('')), 
      ' ', 
      ' / '), 
    '-somebody-puts-microsoft-out-of-his-misery-please-',
    ' ') 
  FROM project_members a WHERE empName IS NOT NULL GROUP BY empName

Кстати, использование «Фамилии» - это опечатка, или я не понимаю концепцию здесь?

В любом случае, большое спасибо, ребята, потому что это сэкономило мне довольно много времени :)

Пробовал их, но для моих целей в MS SQL Server 2005 наиболее полезными были следующие, которые я нашел в xaprb

declare @result varchar(8000);

set @result = '';

select @result = @result + name + ' '

from master.dbo.systypes;

select rtrim(@result);

@Mark, как вы упомянули, у меня проблемы с пробелом.

Я могу немного опоздать на вечеринку, но этот метод работает для меня и проще, чем метод COALESCE.

SELECT STUFF(
             (SELECT ',' + Column_Name 
              FROM Table_Name
              FOR XML PATH (''))
             , 1, 1, '')

Чтобы объединить все имена менеджеров проектов из проектов, в которых есть несколько менеджеров, напишите:

SELECT a.project_id,a.project_name,Stuff((SELECT N'/ ' + first_name + ', '+last_name FROM projects_v 
where a.project_id=project_id
 FOR
 XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N''
) mgr_names
from projects_v a
group by a.project_id,a.project_name

Взгляните на проект GROUP_CONCAT на Github, думаю, я делаю именно то, что вы ищете:

This project contains a set of SQLCLR User-defined Aggregate functions (SQLCLR UDAs) that collectively offer similar functionality to the MySQL GROUP_CONCAT function. There are multiple functions to ensure the best performance based on the functionality required...

SQL Server 2017 г. вводит новую агрегатную функцию

STRING_AGG ( expression, separator).

Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

Объединенные элементы можно упорядочить, добавив WITHIN GROUP (ORDER BY some_expression)

Для версий 2005-2016 Я обычно использую метод XML в принятом ответе.

Однако в некоторых случаях это может не сработать. например если объединяемые данные содержат CHAR(29), вы увидите

FOR XML could not serialize the data ... because it contains a character (0x001D) which is not allowed in XML.

Более надежным методом, который может работать со всеми символами, было бы использование агрегата CLR. Однако с этим подходом сложнее упорядочить конкатенированные элементы.

Метод присвоения переменной - не гарантировано, и его следует избегать в производственном коде.

Для моих коллег из Google, вот очень простое решение plug-and-play, которое сработало для меня после того, как некоторое время боролся с более сложными решениями:

SELECT
distinct empName,
NewColumnName=STUFF((SELECT ','+ CONVERT(VARCHAR(10), projID ) 
                     FROM returns 
                     WHERE empName=t.empName FOR XML PATH('')) , 1 , 1 , '' )
FROM 
returns t

Обратите внимание, что мне пришлось преобразовать идентификатор в VARCHAR, чтобы объединить его как строку. Если вам не нужно этого делать, вот еще более простая версия:

SELECT
distinct empName,
NewColumnName=STUFF((SELECT ','+ projID
                     FROM returns 
                     WHERE empName=t.empName FOR XML PATH('')) , 1 , 1 , '' )
FROM 
returns t

Вся заслуга в этом здесь: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9508abc2-46e7-4186-b57f-7f368374e084/replicating-groupconcat-function-of-mysql-in-sql-server?forum=transactsql

ОБНОВЛЕНИЕ 2020: SQL Server 2016+JSON Примеры сериализации и десериализации

Данные, предоставленные OP, вставлены во временную таблицу с именем #project_members

drop table if exists #project_members;
create table #project_members(
  empName        varchar(20) not null,
  projID         varchar(20) not null);
go
insert #project_members(empName, projID) values
('ANDY', 'A100'),
('ANDY', 'B391'),
('ANDY', 'X010'),
('TOM', 'A100'),
('TOM', 'A510');

Как сериализовать эти данные в одну строку JSON с вложенным массивом, содержащим идентификаторы projID

select empName, (select pm_json.projID 
                 from #project_members pm_json 
                 where pm.empName=pm_json.empName 
                 for json path, root('projList')) projJSON
from #project_members pm
group by empName
for json path;

Результат

'[
  {
    "empName": "ANDY",
    "projJSON": {
      "projList": [
        { "projID": "A100" },
        { "projID": "B391" },
        { "projID": "X010" }
      ]
    }
  },
  {
    "empName": "TOM",
    "projJSON": {
      "projList": [
        { "projID": "A100" },
        { "projID": "A510" }
      ]
    }
  }
]'

Как десериализовать эти данные из одной строки JSON обратно в исходные строки и столбцы

declare @json           nvarchar(max)=N'[{"empName":"ANDY","projJSON":{"projList":[{"projID":"A100"},
                                         {"projID":"B391"},{"projID":"X010"}]}},{"empName":"TOM","projJSON":
                                         {"projList":[{"projID":"A100"},{"projID":"A510"}]}}]';

select oj.empName, noj.projID 
from openjson(@json) with (empName        varchar(20),
                           projJSON       nvarchar(max) as json) oj
     cross apply openjson(oj.projJSON, '$.projList') with (projID    varchar(20)) noj;

Полученные результаты

empName projID
ANDY    A100
ANDY    B391
ANDY    X010
TOM     A100
TOM     A510

Как сохранить уникальное имя empName в таблице и сохранить projID во вложенном массиве JSON

drop table if exists #project_members_with_json;
create table #project_members_with_json(
  empName        varchar(20) unique not null,
  projJSON       nvarchar(max) not null);
go
insert #project_members_with_json(empName, projJSON) 
select empName, (select pm_json.projID 
                 from #project_members pm_json 
                 where pm.empName=pm_json.empName 
                 for json path, root('projList')) 
from #project_members pm
group by empName;

Полученные результаты

empName projJSON
ANDY    {"projList":[{"projID":"A100"},{"projID":"B391"},{"projID":"X010"}]}
TOM     {"projList":[{"projID":"A100"},{"projID":"A510"}]}

Как десериализовать из таблицы с уникальным empName и вложенным столбцом массива JSON, содержащим projID

select wj.empName, oj.projID
from
  #project_members_with_json wj
 cross apply
  openjson(wj.projJSON, '$.projList') with (projID    varchar(20)) oj;

Полученные результаты

empName projID
ANDY    A100
ANDY    B391
ANDY    X010
TOM     A100
TOM     A510