1

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
Reda El
  • 11
  • 2
  • Are the NULL values in your data actually strings with the value 'NULL'? you cannot concatenate a NULL like you show in your expected results otherwise. – Stu Dec 02 '22 at 22:03

2 Answers2

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

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
  • Thank you so much for your answer, it is possible to create function which can chieve the same thing that string_agg function does , in older version of sql management server ? – Reda El Dec 03 '22 at 14:09
  • I highly recommend to do not use obsolete versions and recreate functions that already exist in better and newer versions and I refuse to deal with such stuff ;) But please see the link I added in my last sentence, that's exactly about this topic. – Jonas Metzler Dec 03 '22 at 14:42
0

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

nbk
  • 45,398
  • 8
  • 30
  • 47
  • Thank you for your help, can you tel me if it is possible to replace the null value with space and get , before the second element for the same id for exemple : the id FR : ,1,3 – Reda El Dec 03 '22 at 14:07