How to concatenate all the rows linked with a same id into one row? consedring null values

Question

I'm trying to concatenate all rows for same ID, the rows for a ID can have null or empty value:

country	value
FR	NULL
FR	1
FR	3
MA	5
MA	NULL
MA	4
ES	9
ES	10
ES	NULL

I would like to consider this case in my query to get this result:

country	value
FR	NULL,1,3
MA	4,NULL,9
ES	9,10,NULL

We can consider to replace null value to get this result

country	value
FR	,1,3
MA	4,,9
ES	9,10,

sql server version : Microsoft SQL Server 2014 (SP2-GDR) (KB4505217) - 12.0.5223.6 (X64)

I have tried this query

SELECT IDENT_0, PAYS_0 = STUFF

SELECT ', ' + TEXTE_0
FROM UAI.YORIGINELOT AS T2
LEFT JOIN UAI.ATEXTRA ON T2.YOMP_0 = ATEXTRA.IDENT1_0 AND CODFIC_0 = 'TABCOUNTRY' AND LANGUE_0 = 'FRA' And ZONE_0 = 'CRYDES'
WHERE T2.IDENT_0 = T1.IDENT_0
ORDER BY IDENT_0
FOR XML PATH (''), TYPE
     ).value('.', 'varchar(max)')
1, 1, '')
FROM UAI.YORIGINELOT AS T1
LEFT JOIN UAI.ATEXTRA ON T1.YFABEN_0 = ATEXTRA.IDENT1_0 AND LANGUE_0='FRA' AND CODFIC_0='TABCOUNTRY' AND ZONE_0 = 'CRYDES'
WHERE OBJ_0 = 'ITM'  
GROUP BY IDENT_0

Thank you

Answer 1

Since SQLServer 2017, we can use STRING_AGG to produce the expected result.

In order to replace NULL values by any other string - even if it just should be "NULL" - we can use COALESCE.

So this query will do:

SELECT country, 
STRING_AGG(COALESCE(value,'NULL'),',') AS value
FROM yourtable
GROUP BY country
ORDER BY country;

Of course, this is just a sample based on one table because I don't know your table structure. Just use this concept in your query. The result of this query will be this one:

country	value
ES	9,10,NULL
FR	NULL,1,3
MA	5,NULL,4

Try out: db<>fiddle

Here the documentation about STRING_AGG

If you still use an older version, I highly recommend to update.

If this isn't possible, there are lot of articles (for example here on SO) how to do this with other functions. Here one of them: Question on SO

Answer 2

As you have SQL server 2014, you better use sTUFF

But you should consoder an upgrade, as the ned of life was Jul 9, 2019 so if you haven't an extended support, you will become vulnerable.l

SELECT country,
    val = 
    STUFF (
        (SELECT   
                ',' +value  
        FROM yourtable
  WHERE value IS NOT NULL AND y1.country = country
        FOR XML PATH('')
  
  ), 1, 1, ''
  
    )
FROM yourtable y1
GROUP by country
ORDER BY country

country	val
ES	9,10
FR	1,3
MA	5,4

fiddle

If you want also the NULL values represnted

SELECT country,
    val = 
    STUFF (
        (SELECT   
                ',' + COALESCE(value,'')  
        FROM yourtable
  WHERE  y1.country = country
  ORDER BY COALESCE(value,0)
        FOR XML PATH('')
  
  ), 1, 1, ''
  
    )
FROM yourtable y1
GROUP by country
ORDER BY country

country	val
ES	,9,10
FR	,1,3
MA	,4,5

fiddle