1

Closed

GROUP_CONCAT does not keep the INSERTorder

description

Hello
i am using GROUP_CONCAT. it is an amazing tool when i need aggregate more than one column but the function does not keep the INSERT order

Example

DECLARE @t AS TABLE (a CHAR(1), b int)
INSERT INTO @t
VALUES ('a',1),('b',2),('c',3),('a',2)

-- Data
SELECT * FROM @t

-- GROUP_CONCAT
SELECT dbo.GROUP_CONCAT(a) AS a ,dbo.GROUP_CONCAT(b) AS b FROM @t

-- XML 
SELECT ISNULL(STUFF((SELECT ','+a FROM @t
       FOR XML PATH(''),TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, ''), '') AS a,
       ISNULL(STUFF((SELECT ','+CAST(b AS VARCHAR(15)) FROM @t
       FOR XML PATH(''),TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, ''), '') AS b

Result

a    b
---- -----------
a    1
b    2
c    3
a    2

--CLR
a               b
--------------- ---------------
a,a,b,c         1,2,2,3

--XML
a               b
--------------- ---------------
a,b,c,a         1,2,3,2

Closed Feb 27, 2016 at 2:10 PM by opcthree

comments

opcthree wrote Sep 18, 2014 at 4:55 AM

Insert-order is not considered within GROUP_CONCAT due to the internal data structures that are used, which were chosen for efficiency. This is very similar to how all SQL works in that sort-order in not guaranteed unless an ORDER BY is provided, which is the case with the XML too, you just haven't seen anything but anecdotal evidence so far. If you want sorting for values within a specific aggregation use GROUP_CONCAT_S, although from your example that likely will not give you want you are after either.

Without an ORDER BY the query using XML is also prone to having the order of the results change between executions, i.e. there is no guarantee that the columns in your resultset will remain sorted in lockstep with one another.

To really get what you need, you need to include a unique column in both of your aggregations to use as a sort-key, like this:
DECLARE @t AS TABLE
(
 id INT NOT NULL
        IDENTITY(1, 1),
 a CHAR(1),
 b INT
)
INSERT  INTO @t
VALUES  ('a', 1),
        ('b', 2),
        ('c', 3),
        ('a', 2)

-- Data
SELECT  *
FROM    @t

-- GroupConcatAltSorted
SELECT  dbo.GroupConcatAltSorted(a,id,3) AS a,
        dbo.GroupConcatAltSorted(b,id,3) AS b
FROM    @t

-- XML 
SELECT  ISNULL(STUFF((SELECT    ',' + a
                      FROM      @t
                      ORDER BY  id
               FOR   XML PATH(''),
                         TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, ''), '') AS a,
        ISNULL(STUFF((SELECT    ',' + CAST(b AS VARCHAR(15))
                      FROM      @t
                      ORDER BY  id
               FOR   XML PATH(''),
                         TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, ''), '') AS b
Note that dbo.GroupConcatAltSorted has not been released yet as it is not fully tested but the code already exists in the /Dev/SortingFeatures branch if you want to check it out.

https://groupconcat.codeplex.com/SourceControl/latest#Dev/SortingFeatures/GroupConcat/GroupConcatAltSorted.cs

burgosjuanpa wrote Sep 18, 2014 at 4:26 PM

hi opcthree
ok, thank you very much for your quick reply.

wrote Feb 27, 2016 at 2:10 PM