SQL Concat rows to variable

Question

I need to build a function that return a string containing values from a table. So I declare a variable NVARCHAR and I use the following code using concat to add each row to the string.

DECLARE @Comment AS NVARCHAR(max) = ''

SELECT @Comment =
    @Comment + 
    CONCAT (
        replace(space(100), N' ', N'-')
        ,CHAR(13)
        ,convert(NVARCHAR(100), T.DT, 103)
        ,N' '
        ,convert(NVARCHAR(5), T.DT, 114)
        ,N' - '
        ,isnull(URESP.N_UTIL + N' ' + URESP.PRE_UTIL, 'System')
        ,N' : '
        ,isnull(TA.L_TACTION, T.ACT)
        ,CHAR(13)
        ,isnull(T.TXT , N' ')
        ,CHAR(13)
        )
FROM (
    SELECT D_CREATION DT
        ,'Commentaire' ACT
        ,I_C_UTIL_CREA C_UTIL
        ,L_COMMENT TXT
    FROM ACTIONS
    WHERE NO_APPEL = 106984 and C_TACTION = 'I_CR_INT'
    ) T
LEFT JOIN UTILISATEUR URESP ON URESP.C_UTIL = T.C_UTIL
LEFT JOIN TACTION TA ON TA.C_TACTION = ACT
ORDER BY DT desc

PRINT @Comment

Output:

----------------------------------------------------------------------------------------------------
19/07/2018 08:46 - ROCH Charly : Commentaire
test

I can't understand why the string contain only the first row. While the query under

SELECT T.* FROM (
    SELECT D_CREATION DT
        ,'Commentaire' ACT
        ,I_C_UTIL_CREA C_UTIL
        ,L_COMMENT TXT
    FROM ACTIONS
    WHERE NO_APPEL = 106984 and C_TACTION = 'I_CR_INT'
    ) T
LEFT JOIN UTILISATEUR URESP ON URESP.C_UTIL = T.C_UTIL
LEFT JOIN TACTION TA ON TA.C_TACTION = ACT
ORDER BY DT desc

returns the 2 rows.

DT                      ACT         C_UTIL  TXT
2018-07-19 08:50:41.470 Commentaire 14254   test2
2018-07-19 08:46:51.240 Commentaire 14254   test

score 2 · Answer 1 · answered Jul 19 '18 at 08:23

Are you sure the content of all tables are right ? When I test a similar query it works fine. Example :

create table COM (uid integer, comment varchar(50));
insert into COM values(1, 'Com 1');
insert into COM values(2, 'Com 2');
insert into COM values(2, 'Com 3');

create table UID (uid integer, name varchar(50));
insert into UID values(1, 'User 1');
insert into UID values(2, 'User 2');
insert into UID values(3, 'User 3');

declare @test as nvarchar(500) = ''
select @test = concat(@test, ' ', COM.uid, ' ', COM.comment, ' ', UID.name, ' | ')
from COM, UID
where COM.uid = UID.uid and UID.uid = 2
select RTRIM(SUBSTRING(RTRIM(@test), 1, LEN(@test)-1))

This give me the result :

2 Com 2 User 2 | 2 Com 3 User 2

score 1 · Answer 2 · answered Jul 19 '18 at 07:55

1

The Concat function is not an aggregate function, it concatenates as many strings as you pass as parameters, not rows.

https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-2017

To concatenate rows you have to use the string_agg function.

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

But this is only available since SQL Server 2017. If you are using an older version, then you will have to use a trick explained here.

How to concatenate text from multiple rows into a single text string in SQL server?

answered Jul 19 '18 at 07:55

Marc Guillot

6,090
1
15
42

Weird! if I use : @COMMENT + CONCAT (T.TXT,CHAR(13)) It works well and returns the 2 comments :( I guess there is something with what I put in the concat – Charly Roch Jul 19 '18 at 08:04
Yes, this is a trick, CONCAT was not intended to be used as an aggregate function that way. Just a couple of days ago another user got problems using it. https://stackoverflow.com/questions/51315886/concatenating-nvarcharmax-values-doesnt-seem-to-work-working-as – Marc Guillot Jul 19 '18 at 08:49

score 0 · Answer 3 · answered Jul 19 '18 at 10:49

Thanks for your answer I got one by myself as well. Not sure if it's the best way to do it but it works for now.

DECLARE @Comment AS NVARCHAR(max) = ''

    SELECT @Comment =
    --  Ligne de séparation des actions 
    @Comment + T.TXT
FROM (
    SELECT CONCAT (
            replace(space(100), N' ', N'-')
            ,CHAR(13)
            -- Date et heure de création de l'action  
            ,convert(NVARCHAR(100), A.D_CREATION, 103)
            ,N' '
            ,convert(NVARCHAR(5), A.D_CREATION, 114)
            --Utilisateur qui crée l'action : nom de l'action (L_TACTION) et à défaut le code de l'action
            ,N' - '
            ,isnull(URESP.N_UTIL + N' ' + URESP.PRE_UTIL, 'System')
            ,N' : '
            ,'Commentaire'
            ,CHAR(13)
            ,isnull(A.L_COMMENT, N' ')
            ,CHAR(13)
            ) TXT
    FROM ACTIONS A
    LEFT JOIN UTILISATEUR URESP ON URESP.C_UTIL = A.C_UTIL
    LEFT JOIN TACTION TA ON TA.C_TACTION = A.C_TACTION
    WHERE NO_APPEL = 106984
        AND A.C_TACTION = 'I_CR_INT'
    ) T
    RETURN @Comment

I am now building a table containing my data already formatted into a string. So I only have to put all the rows together and we're done.

SQL Concat rows to variable

3 Answers3