Apr 26, 2014

GROUP_CONCAT aggregate function in sql server

Sql server has not inbuilt GROUP_CONCAT function to get aggregate data in concatenated format just like MYSQL. Sql server has power of CLR objects we can create this aggregate function.  

I have already created user defined GROUP_CONCAT function in sql server. To use this you have to only execute this script one time:

EXECUTE SP_CONFIGURE 'clr enabled', 1
GO

RECONFIGURE
GO

ALTER DATABASE <YourDatabaseName> SET TRUSTWORTHY ON

GO

ALTER AUTHORIZATION ON DATABASE:: <YourDatabaseName> TO sa

GO

CREATE ASSEMBLY [CLRAggrigates]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0104009B7B5B530000000000000000E00002210B010800000C0000000E0000000000002E2A000000200000004000000000400000200000000200000400000000000000040000000000000000A000000004000000000000020040850000100000100000000010000010000000000000100000000000000000000000E02900004B000000006000005008000000000000000000000000000000000000008000000C000000004000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000340A000000200000000C000000040000000000000000000000000000200000602E7364617461000084000000004000000002000000100000000000000000000000000000400000C02E727372630000005008000000600000000A000000120000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000001C0000000000000000000000000000400000420000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000102A000000000000480000000200050028210000B808000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002602280300000A00002A00004E00027E0400000A280500000A7D01000004002A13300300420000000000000000027B010000047E0400000A280500000A280600000A280700000A2C0902037D010000042B190002027B0100000404280800000A03280800000A7D0100000400002A00006A0002036F050000067E0400000A280500000A6F0300000600002A00133001000C0000000100001100027B010000040A2B00062A520002036F0900000A280500000A7D01000004002A0000006E0003027C01000004FE16030000016F0A00000A6F0B00000A00002A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000004030000237E0000700300009003000023537472696E6773000000000007000008000000235553000807000010000000234755494400000018070000A001000023426C6F620000000000000002000001571702020900000000FA253300160000010000001900000002000000010000000700000006000000010000001B000000110000000100000002000000010000000300000000007D0301000000000006004B0044000E0079005E000E00A50090000600F600EC0006000A01EC0006001F0144000E00380190000E0077015E000E0098015E0006009F0144000E00B5015E000600DA01C7010600F701C70137000B02000006003A021A0206005A021A020600970278020600A50278020600B90244000600E102CF020600FC02CF0206001703CF0206003003CF0206004903CF0206006603CF02000000000100000000000100010001200000290036000500010001000100AF00170050200000000006188A00130001005C20000000000600BC00130001007020000000000600C1001B000100C020000000000600DC0023000300DC20000000000600E20029000400F42000000000660303012E0005000C2100000000660317013400060000000100CC0000000200D20000000100CC00000000000000000001000801000001001D0102000900110003012E0011001701340009008A001300310026013A0019002C013D0019004301430039004F014C0019005701520021006301600009006E01600029001701640041008A00690051008A00130059008A00130061008A00130069008A00FC0079008A00020181008A00130089008A00640091008A00070199008A000701A1008A006400A9008A006400B1008A006400B9008A006400C1008A006400C9008A00640020007B00F70024007300D1002E009B003D012E00A30067012E00AB006D012E00DB008B012E008B0015012E0093001E012E00B30067012E00BB0073012E00D30067012E0083000C012E00C3008B012E00CB006701430063006F0044007300E40084007300D1005B0002000C00030002000E00050004800000010000006D147E4D0000000000003600000002000000000000000000000001000A00000000000800000000000000000000000A00130000000000020000000000000000000000010052000000000000000000003C4D6F64756C653E006D73636F726C6962004D6963726F736F66742E56697375616C42617369630047524F55505F434F4E43415400434C52416767726967617465730053797374656D004F626A6563740053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A65002E63746F720053797374656D2E446174612E53716C54797065730053716C537472696E670053747241676772696761746500496E697400416363756D756C6174650076616C756500536570617261746F72004D65726765005465726D696E6174650053797374656D2E494F0042696E617279526561646572005265616400720042696E617279577269746572005772697465007700537472696E6700456D707479006F705F496D706C696369740053716C426F6F6C65616E006F705F457175616C697479006F705F54727565006F705F4164646974696F6E0052656164537472696E6700546F537472696E670053716C55736572446566696E656441676772656761746541747472696275746500466F726D61740053657269616C697A61626C654174747269627574650053716C46616365744174747269627574650053797374656D2E446961676E6F73746963730044656275676765724E6F6E55736572436F64654174747269627574650044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E496E7465726F705365727669636573004775696441747472696275746500436F6D56697369626C6541747472696275746500434C53436F6D706C69616E744174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C6541747472696275746500434C52416767726967617465732E646C6C000000032000000000008DD472E8019E5B428A80BB565483CA350008B77A5C561934E08908B03F5F7F11D50A3A032000010306110D07200201110D110D052001011208042000110D05200101121105200101121502060E050001110D0E080002111D110D110D05000102111D080002110D110D110D040701110D0320000E042001010E0520010111256101000200000004005402124973496E76617269616E74546F4E756C6C73015402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720054080B4D61784279746553697A65FFFFFFFF12010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65320000000401000000052001011139042001010804200101020801000301000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773012901002439383136656564642D613461642D346137312D383332362D343532393063303939376635000005010000000005010001000017010012436F7079726967687420C2A920203230313400001201000D434C524167677269676174657300000000082A000000000000000000001E2A0000002000000000000000000000000000000000000000000000102A00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009B7B5B530000000002000000680000001C4000001C100000525344534D70576EC35D384D9BF3F7F5BCBCC6F101000000463A5C50726F6A656374735C50726F6A656374323031345C434C52416767726967617465735C434C52416767726967617465735C6F626A5C44656275675C434C52416767726967617465732E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030003000000280000800E000000480000801000000060000080000000000000000000000000000002000200000078000080030000009000008000000000000000000000000000000100007F0000A80000800000000000000000000000000000010001000000C00000800000000000000000000000000000010000000000D80000000000000000000000000000000000010000000000E80000000000000000000000000000000000010000000000F800000000000000000000000000000000000100000000000801000018640000E80200000000000000000000006700002801000000000000000000002868000022000000000000000000000018610000000300000000000000000000000334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001007E4D6D14000001007E4D6D143F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00460020000010053007400720069006E006700460069006C00650049006E0066006F0000003C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E000000000043004C0052004100670067007200690067006100740065007300000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0035003200320039002E00310039003800330038000000000044001200010049006E007400650072006E0061006C004E0061006D006500000043004C00520041006700670072006900670061007400650073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100340000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C00520041006700670072006900670061007400650073002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D0065000000000043004C0052004100670067007200690067006100740065007300000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0035003200320039002E00310039003800330038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0035003200320039002E003100390038003300380000000000280000002000000040000000010004000000000080020000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007777777777777777777777777777700444444444444444444444444444447004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF4700488888888888888888888888888847004444444444444444444444444444470044C4C4C4C4C4C4C4C4C4ECECE49747004CCCCCCCCCCCCCCCCCCCCCCCCCCC40000444444444444444444444444444000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFC00000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000003C0000007FFFFFFFFFFFFFFFFFFFFFFFF2800000010000000200000000100040000000000C0000000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF000000000000000000077777777777777744444444444444474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF84748888888888888474CCCCCCCCCCCCC47C4444444444444C000000000000000000000000000000000FFFF000080000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000FFFF0000FFFF00000000010002002020100001000400E802000002001010100001000400280100000300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000303A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

CREATE AGGREGATE [dbo].[GROUP_CONCAT](
@value [nvarchar](max),
@Separator [nvarchar](50))
RETURNS[nvarchar](max)
EXTERNAL NAME [CLRAggrigates].[CLRAggrigates.GROUP_CONCAT]
GO

Note: In the above script replace <YourDatabaseName> by your database name.

You must have sufficient permission execute above script. If you face any problems please put your note in the comment section.

Now you can use group_conact aggregate function. For examples:

SELECT
ntUserID,
    dbo.GROUP_CONCAT([ntQuantity],',') AS vcQuantity
 FROM tblOrder
 GROUP BY ntUserID

Sample output:


ntUserID
vcQuantity
1
20,15,15
2
20,15,15
3
20,15,15
4
20,15,15

9 comments:

  1. Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near '.'.
    Msg 195, Level 15, State 5, Line 2
    'TRUSTWORTHY' is not a recognized SET option.
    Msg 15151, Level 16, State 1, Line 2
    Cannot find the database 'SIZERUNS', because it does not exist or you do not have permission.
    Msg 10327, Level 14, State 1, Line 2
    CREATE ASSEMBLY for assembly 'CLRAggrigates' failed because assembly 'CLRAggrigates' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
    Msg 6528, Level 16, State 1, Line 2
    Assembly 'CLRAggrigates' was not found in the SQL catalog of database 'master'.

    THIS IS what was returned when I executed your code. I don't know how to run the RECONFIGURE statement to install. I'm not quite sure if anything was actually done with your code or not. Advise?
    thanks

    ReplyDelete
    Replies
    1. Hi Angela

      From the error message it is obvious your are passing incorrect database name.

      ALTER DATABASE SET TRUSTWORTHY ON

      Simply replace by your database name without any single quote etc (verify database SIZERUNS is correct or not).

      Also from the error message "Incorrect syntax near '.' " above line contains dot. Check it why there is dot symbol.

      If you still getting error message please send your full script which are you executing to my email riteshkiitian@gmail.com


      Delete
  2. The function creates ok for me, however when attempting to use i get the following error.
    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "GROUP_CONCAT":
    System.Data.SqlTypes.SqlTypeException: Two strings to be compared have different collation.
    System.Data.SqlTypes.SqlTypeException:
    at System.Data.SqlTypes.SqlString.StringCompare(SqlString x, SqlString y)
    at System.Data.SqlTypes.SqlString.Compare(SqlString x, SqlString y, EComparison ecExpectedResult)
    at System.Data.SqlTypes.SqlString.op_Equality(SqlString x, SqlString y)
    at CLRAggrigates.GROUP_CONCAT.Accumulate(SqlString value, SqlString Separator)
    .


    Table that I am querying:
    CREATE TABLE cities
    (
    city nVARCHAR(70),
    state nVARCHAR(2)
    );

    INSERT INTO cities VALUES ('San Francisco', 'CA');
    INSERT INTO cities VALUES ('San Diego', 'CA');
    INSERT INTO cities VALUES ('Los Angeles', 'CA');

    INSERT INTO cities VALUES ('Austin', 'TX');
    INSERT INTO cities VALUES ('Houston', 'TX');

    Both columns have the same collation.

    Any ideas?

    Thanks,
    Peter

    ReplyDelete
    Replies
    1. I have executed this script and worked fine for me:

      SELECT
      state,
      dbo.GROUP_CONCAT(city,',') AS vcQuantity
      FROM cities
      GROUP BY state

      Delete
    2. Hi Ritesh,

      Yeah, I executed that script as well.

      Msg 6522, Level 16, State 1, Line 1
      A .NET Framework error occurred during execution of user-defined routine or aggregate "GROUP_CONCAT":
      System.Data.SqlTypes.SqlTypeException: Two strings to be compared have different collation.
      System.Data.SqlTypes.SqlTypeException:
      at System.Data.SqlTypes.SqlString.StringCompare(SqlString x, SqlString y)
      at System.Data.SqlTypes.SqlString.Compare(SqlString x, SqlString y, EComparison ecExpectedResult)
      at System.Data.SqlTypes.SqlString.op_Equality(SqlString x, SqlString y)
      at CLRAggrigates.GROUP_CONCAT.Accumulate(SqlString value, SqlString Separator)

      But get this error each time.

      Using sql server 2012

      Kind Regards,
      Peter



      Delete
    3. Hi,

      I got the same error:

      Msg 6522, Level 16, State 1, Line 45
      A .NET Framework error occurred during execution of user-defined routine or aggregate "GROUP_CONCAT":
      System.Data.SqlTypes.SqlTypeException: Two strings to be compared have different collation.
      System.Data.SqlTypes.SqlTypeException:
      at System.Data.SqlTypes.SqlString.StringCompare(SqlString x, SqlString y)
      at System.Data.SqlTypes.SqlString.Compare(SqlString x, SqlString y, EComparison ecExpectedResult)
      at System.Data.SqlTypes.SqlString.op_Equality(SqlString x, SqlString y)
      at CLRAggrigates.GROUP_CONCAT.Accumulate(SqlString value, SqlString Separator)

      It might be because "When you create a common language runtime (CLR) routine, and a parameter of a CLR method bound to the routine is of type SQLString, SQL Server creates an instance of the parameter with the default collation of the database containing the calling routine. If a parameter is not a SqlType (for example, String rather than SQLString), the collation information from the database is not associated with the parameter." But I have no idea how to fix it.

      Delete
  3. Is there anyway to order the items in the concat in alphabetical order?

    ReplyDelete
  4. Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
    how to solve this error..

    ReplyDelete
  5. When i execute these whole statements
    it will work in my local pc.
    but when i post this script to my end user server

    it will executed...
    but query thow this error..
    please provide me the solution


    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "GROUP_CONCAT":
    System.Data.SqlTypes.SqlTypeException: Two strings to be compared have different collation.
    System.Data.SqlTypes.SqlTypeException:
    at System.Data.SqlTypes.SqlString.StringCompare(SqlString x, SqlString y)
    at System.Data.SqlTypes.SqlString.Compare(SqlString x, SqlString y, EComparison ecExpectedResult)
    at System.Data.SqlTypes.SqlString.op_Equality(SqlString x, SqlString y)
    at CLRAggrigates.GROUP_CONCAT.Accumulate(SqlString value, SqlString Separator)

    ReplyDelete