Simulating group concat MySQL function in Microsoft SQL Server 2005

Question

I m trying to migrate a MySQL-based app over to Microsoft SQL Server 2005  not by choice  but that s life    In the original app  we used almost entirely ANSI-SQL compliant statements  with one significant exception -- we used MySQL s group concat function fairly frequently   group concat  by the way  does this  given a table of  say  employee names and projects     SELECT empName  projID FROM project members    returns   ANDY      A100 ANDY      B391 ANDY      X010 TOM       A100 TOM       A510       and here s what you get with group concat   SELECT      empName  group concat projID SEPARATOR         FROM      project members  GROUP BY      empName    returns   ANDY      A100   B391   X010 TOM       A100   A510   So what I d like to know is  Is it possible to write  say  a user-defined function in SQL Server which emulates the functionality of group concat    I have almost no experience using UDFs  stored procedures  or anything like that  just straight-up SQL  so please err on the side of too much explanation

User · Answer

Tried these but for my purposes in MS SQL Server 2005 the following was most useful, which I found at xaprb

declare @result varchar(8000);

set @result = '';

select @result = @result + name + ' '

from master.dbo.systypes;

select rtrim(@result);

@Mark as you mentioned it was the space character that caused issues for me.

User · Answer

Have a look at the GROUP CONCAT project on Github  I think I does exactly what you are searching for      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

User · Answer

UPDATE 2020  SQL Server 2016  JSON Serialization and De-serialization Examples The data provided by the OP inserted into a temporary table called  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     How to serialize this data into a single JSON string with a nested array containing projID s 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   Result             quot empName quot    quot ANDY quot        quot projJSON quot            quot projList quot                quot projID quot    quot A100 quot                quot projID quot    quot B391 quot                quot projID quot    quot X010 quot                                quot empName quot    quot TOM quot        quot projJSON quot            quot projList quot                quot projID quot    quot A100 quot                quot projID quot    quot A510 quot                          How to de-serialize this data from a single JSON string back to it s original rows and columns declare  json           nvarchar max  N    quot empName quot   quot ANDY quot   quot projJSON quot    quot projList quot     quot projID quot   quot A100 quot                                               quot projID quot   quot B391 quot     quot projID quot   quot X010 quot        quot empName quot   quot TOM quot   quot projJSON quot                                              quot projList quot     quot projID quot   quot A100 quot     quot projID quot   quot A510 quot          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   Results empName projID ANDY    A100 ANDY    B391 ANDY    X010 TOM     A100 TOM     A510  How to persist the unique empName to a table and store the projID s in a nested JSON array 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   Results empName projJSON ANDY      quot projList quot     quot projID quot   quot A100 quot     quot projID quot   quot B391 quot     quot projID quot   quot X010 quot     TOM       quot projList quot     quot projID quot   quot A100 quot     quot projID quot   quot A510 quot      How to de-serialize from a table with unique empName and nested JSON array column containing projID s select wj empName  oj projID from    project members with json wj  cross apply   openjson wj projJSON     projList   with  projID    varchar 20   oj   Results empName projID ANDY    A100 ANDY    B391 ANDY    X010 TOM     A100 TOM     A510

User · Answer

To concatenate all the project manager names from projects that have multiple project managers write   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

User · Answer

No REAL easy way to do this  Lots of ideas out there  though   Best one I ve found   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    Or a version that works correctly if the data might contain characters such as  lt   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

User · Answer

I may be a bit late to the party but this method works for me and is easier than the COALESCE method   SELECT STUFF                SELECT       Column Name                FROM Table Name               FOR XML PATH                      1  1

User · Answer

No REAL easy way to do this  Lots of ideas out there  though   Best one I ve found   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    Or a version that works correctly if the data might contain characters such as  lt   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

User · Answer

Tried these but for my purposes in MS SQL Server 2005 the following was most useful, which I found at xaprb

declare @result varchar(8000);

set @result = '';

select @result = @result + name + ' '

from master.dbo.systypes;

select rtrim(@result);

@Mark as you mentioned it was the space character that caused issues for me.

User · Answer

With the below code you have to set PermissionLevel External on your project properties before you deploy  and change the database to trust external code  be sure to read elsewhere about security risks and alternatives  like certificates   by running  ALTER DATABASE database name SET TRUSTWORTHY ON    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 lt string gt         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              I ve tested this using a query that looks like   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    And yields  A  B  C  D

User · Answer

About J Hardiman s answer  how about   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   By the way  is the use of  Surname  a typo or am i not understanding a concept here   Anyway  thanks a lot guys cuz it saved me quite some time

User · Answer

Have a look at the GROUP CONCAT project on Github  I think I does exactly what you are searching for      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

User · Answer

Possibly too late to be of benefit now  but is this not the easiest way to do things   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

User · Answer

No REAL easy way to do this  Lots of ideas out there  though   Best one I ve found   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    Or a version that works correctly if the data might contain characters such as  lt   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

User · Answer

SQL Server 2017 does introduce a new aggregate function  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    The concatenated elements can be ordered by appending WITHIN GROUP  ORDER BY some expression   For versions 2005-2016 I typically use the XML method in the accepted answer   This can fail in some circumstances however  e g  if the data to be concatenated contains CHAR 29  you see     FOR XML could not serialize the data     because it   contains a character  0x001D  which is not allowed in XML    A more robust method that can deal with all characters would be to use a CLR aggregate  However applying an ordering to the concatenated elements is more difficult with this approach   The method of assigning to a variable is not guaranteed and should be avoided in production code

User · Answer

With the below code you have to set PermissionLevel External on your project properties before you deploy  and change the database to trust external code  be sure to read elsewhere about security risks and alternatives  like certificates   by running  ALTER DATABASE database name SET TRUSTWORTHY ON    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 lt string gt         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              I ve tested this using a query that looks like   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    And yields  A  B  C  D

User · Answer

I may be a bit late to the party but this method works for me and is easier than the COALESCE method   SELECT STUFF                SELECT       Column Name                FROM Table Name               FOR XML PATH                      1  1

User · Answer

No REAL easy way to do this  Lots of ideas out there  though   Best one I ve found   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    Or a version that works correctly if the data might contain characters such as  lt   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

User · Answer

To concatenate all the project manager names from projects that have multiple project managers write   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

User · Answer

UPDATE 2020  SQL Server 2016  JSON Serialization and De-serialization Examples The data provided by the OP inserted into a temporary table called  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     How to serialize this data into a single JSON string with a nested array containing projID s 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   Result             quot empName quot    quot ANDY quot        quot projJSON quot            quot projList quot                quot projID quot    quot A100 quot                quot projID quot    quot B391 quot                quot projID quot    quot X010 quot                                quot empName quot    quot TOM quot        quot projJSON quot            quot projList quot                quot projID quot    quot A100 quot                quot projID quot    quot A510 quot                          How to de-serialize this data from a single JSON string back to it s original rows and columns declare  json           nvarchar max  N    quot empName quot   quot ANDY quot   quot projJSON quot    quot projList quot     quot projID quot   quot A100 quot                                               quot projID quot   quot B391 quot     quot projID quot   quot X010 quot        quot empName quot   quot TOM quot   quot projJSON quot                                              quot projList quot     quot projID quot   quot A100 quot     quot projID quot   quot A510 quot          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   Results empName projID ANDY    A100 ANDY    B391 ANDY    X010 TOM     A100 TOM     A510  How to persist the unique empName to a table and store the projID s in a nested JSON array 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   Results empName projJSON ANDY      quot projList quot     quot projID quot   quot A100 quot     quot projID quot   quot B391 quot     quot projID quot   quot X010 quot     TOM       quot projList quot     quot projID quot   quot A100 quot     quot projID quot   quot A510 quot      How to de-serialize from a table with unique empName and nested JSON array column containing projID s select wj empName  oj projID from    project members with json wj  cross apply   openjson wj projJSON     projList   with  projID    varchar 20   oj   Results empName projID ANDY    A100 ANDY    B391 ANDY    X010 TOM     A100 TOM     A510

User · Answer

For my fellow Googlers out there  here s a very simple plug-and-play solution that worked for me after struggling with the more complex solutions for a while   SELECT distinct empName  NewColumnName STUFF  SELECT      CONVERT VARCHAR 10   projID                         FROM returns                       WHERE empName t empName FOR XML PATH        1   1        FROM  returns t   Notice that I had to convert the ID into a VARCHAR in order to concatenate it as a string  If you don t have to do that  here s an even simpler version   SELECT distinct empName  NewColumnName STUFF  SELECT      projID                      FROM returns                       WHERE empName t empName FOR XML PATH        1   1        FROM  returns t   All credit for this goes to here  https   social msdn microsoft com Forums sqlserver en-US 9508abc2-46e7-4186-b57f-7f368374e084 replicating-groupconcat-function-of-mysql-in-sql-server forum transactsql

User · Answer

Possibly too late to be of benefit now  but is this not the easiest way to do things   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

User · Answer

For my fellow Googlers out there  here s a very simple plug-and-play solution that worked for me after struggling with the more complex solutions for a while   SELECT distinct empName  NewColumnName STUFF  SELECT      CONVERT VARCHAR 10   projID                         FROM returns                       WHERE empName t empName FOR XML PATH        1   1        FROM  returns t   Notice that I had to convert the ID into a VARCHAR in order to concatenate it as a string  If you don t have to do that  here s an even simpler version   SELECT distinct empName  NewColumnName STUFF  SELECT      projID                      FROM returns                       WHERE empName t empName FOR XML PATH        1   1        FROM  returns t   All credit for this goes to here  https   social msdn microsoft com Forums sqlserver en-US 9508abc2-46e7-4186-b57f-7f368374e084 replicating-groupconcat-function-of-mysql-in-sql-server forum transactsql

User · Answer

SQL Server 2017 does introduce a new aggregate function  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    The concatenated elements can be ordered by appending WITHIN GROUP  ORDER BY some expression   For versions 2005-2016 I typically use the XML method in the accepted answer   This can fail in some circumstances however  e g  if the data to be concatenated contains CHAR 29  you see     FOR XML could not serialize the data     because it   contains a character  0x001D  which is not allowed in XML    A more robust method that can deal with all characters would be to use a CLR aggregate  However applying an ordering to the concatenated elements is more difficult with this approach   The method of assigning to a variable is not guaranteed and should be avoided in production code

User · Answer

About J Hardiman s answer  how about   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   By the way  is the use of  Surname  a typo or am i not understanding a concept here   Anyway  thanks a lot guys cuz it saved me quite some time

[sql] Simulating group_concat MySQL function in Microsoft SQL Server 2005?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to string-aggregation