This project has moved and is read-only. For the latest updates, please go here.
1

Closed

GROUP_CONCAT value of NULL

description

Concatenating NULL values result in a string 'NULL' instead of 'NULL ,NULL,NULL'. This is important when you are concatenating several columns and need to keep the order of the data.

Example:
child: id: img:
1 2,3,4,5,6 img2,img3,img4,img5,img6
2 4,7,8,9,10 img4,img10

Note: id 7,8,9 have 'img' value of NULL, thus are totally removed.

I think that the result should be the following:
child: id: img:
1 2,3,4,5,6 img2,img3,img4,img5,img6
2 4,7,8,9,10 img4,NULL,NULL,NULL,img10
Closed Feb 27, 2016 at 3:10 PM by opcthree

comments

70074512 wrote Jan 30, 2016 at 7:31 AM

I'm currently using something similar to:
dbo.GROUP_CONCAT(CASE WHEN img IS NULL THEN '0' END)

opcthree wrote Feb 4, 2016 at 9:36 AM

Next time please post a code sample that illustrates the problem. I think I got your point from your post:
with cte as (select 1 as child, 2 as id, 'img2' as img
            union all select 1, 3, 'img3'
            union all select 1, 4, 'img4'
            union all select 1, 5, 'img5'
            union all select 1, 6, 'img6'
            union all select 2, 4, 'img4'
            union all select 2, 7, NULL
            union all select 2, 8, NULL
            union all select 2, 9, NULL
            union all select 2, 10, 'img10')
    select child, dbo.GROUP_CONCAT(id), dbo.GROUP_CONCAT(img) 
    from cte
    group by child;
The order of the strings returned in the aggregate is not guaranteed unless you use dbo.GROUP_CONCAT_S. That said, since you are trying to have two different aggregates in the same query coming back with synchronized/sorted results you would need to have them sorted on the same key. At this time there is no aggregate capable of sorting the concatenated results on an alternate key, i.e. on a data point other than what is being concatenated, therefore you should not attempt to use the CLR objects to do what you are attempting.

There is a planned aggregate named GROUP_CONCAT_AS (the AS if for "alternate sort") that addresses your use case but it is still in development and no version has been released.

In the meantime you will need to resort to using one of the XML techniques to solve your issue.

wrote Feb 27, 2016 at 3:10 PM