combine text in node DB Query Reader

[Gfranco2021]

hello, good afternoon.

What function would be correct to generate this information within the DB Query Reader node? In another program I use the function CONCAT(Field,‘,’) as Field1,

Thank you so much.

[izaychik63]

If you use SQL Server see examples her

Combine Rows into String in SQL Server - GeeksforGeeks

Also, as you use groups consider

Parameterized DB Query Reader — NodePit

or

DB Looping — NodePit

nodes.

[Daniel_Weikert]

@Gfranco2021
I assume CTEs can be your friend.
Maybe sth like this as a starting point

 WITH ConsolidatedObservations AS (
    SELECT
        [Cod. Cuenta],
        STRING_AGG(CAST([No.] AS VARCHAR(10)), ',') WITHIN GROUP (ORDER BY [No.]) AS 'No_Concatenated',
        STRING_AGG([Observación], ', ') WITHIN GROUP (ORDER BY [No.]) AS 'Observación_Concatenated'
    FROM
        SOMERANDOMTABLE
    GROUP BY
        [Cod. Cuenta]
)
SELECT
    ROW_NUMBER() OVER (ORDER BY [Cod. Cuenta]) AS 'No.',
    [Cod. Cuenta],
    [No_Concatenated] AS 'No',
    [Observación_Concatenated] AS 'Observación'
FROM
    ConsolidatedObservations
ORDER BY
    [Cod. Cuenta];

br

[takbb]

Similar to your thoughts @Daniel_Weikert , depending on the database, it may be that even this will do it:

select 
listagg(distinct "No.",',') as "No.", 
"Cod. Cuenta",
listagg(distinct "Obseración",',') as "Obseración"
from "PUBLIC"."mydemotable"
group by "Cod. Cuenta"
order by "Cod. Cuenta"

(I think that whether the ordering works is going to depend on whether “Cod. Cuenta” is a numeric or string field, but other than that the output should be right). That would work for Oracle, H2 and probably some others.

@Gfranco2021 , the main thing here is that if you are asking what function to use in a DB Query Reader node, we need to know which database you are using. KNIME doesn’t have a particular dialect of SQL. You need to use whatever SQL works with your database. Hope that helps.