Reputation: 368

SQL Concat rows to variable

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

Upvotes: 0

Answers (3)

Charly Roch

Reputation: 368

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.

Upvotes: 0

CodeKiller

Reputation: 111

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 :