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

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

We can consider to replace null value to get this result

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

CodePudding user response：

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:

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

CodePudding user response：

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

fiddle