Concatenate different rows data

Question

Could you please suggest an approach to get the logic for below

Note: This logic needs to be added for existing JOIN query, which I have finished already.

Question: Table name: T_FULL_DETAILS, Database: SQL Server

The expectation is

select DESCRIPTION from T_FULL_DETAILS WHERE SEQ_ID=3

then it should return "Hellooo.. Good morning Good Afternoon Good evenin"

select DESCRIPTION from T_FULL_DETAILS WHERE SEQ_ID=5

then it should return "This is last record Five "

So here the TEXT_IDX doesn't have any fixed limit. Could you check and help me to write the relevant query in SQL Server?

Reference data is below

SEQ_ID TEXT_IDX DESCRIPTION 3 1 Hellooo.. 3 2 Good morning 3 3 Good Afternoon 3 4 Good evenin 4 1 my number 4 2 four 5 1 This is 5 2 last record 5 3 Five — Johnny, May 07 '20 at 06:16
Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Marc Guillot, May 07 '20 at 06:26
Marc Guillot's comment pretty much answers the question with different ways to acheive this. But you should really do stuff like concatenating query results client-side. — JonC, May 07 '20 at 10:02

Marc Guillot · Accepted Answer · 2020-05-07T06:26:45.957

1

You have the function string_agg to aggregate text fields.

select string_agg(DESCRIPTION, ' ') from T_FULL_DETAILS where SEQ_ID=3

Please notice that this function is only available since SQL Server 2016, if you are using a previous version then you need to find an alternative, like using the XML features:

select DESCRIPTION + ' ' as DESCRIPTION 
from T_FULL_DETAILS 
where SEQ_ID=3 
order by TEXT_IDX
for XML path('')

edited May 07 '20 at 06:26

answered May 07 '20 at 06:15

Marc Guillot

6,090
1
15
42

Thanks for the quick response. it returns string_agg is not a recognized built-in function name. I am working on SQL 2008. Any alternative for this ? – Johnny May 07 '20 at 06:20
@Johnny Hi Johnny, I've added an alternative for older versions of SQL Server. – Marc Guillot May 07 '20 at 06:21

score 0 · Answer 2 · answered May 07 '20 at 06:22

Depending on your version you can use one of the following:

SELECT STRING_AGG(DESCRIPTION, ' ') WITHIN  GROUP (ORDER BY TEXT_IDX ASC)
FROM T_FULL_DETAILS
WHERE SEQ_ID=3

SELECT STUFF
(
    (
        select ' ' + DESCRIPTION  AS DESCRIPTION 
        from T_FULL_DETAILS 
        where SEQ_ID=3 
        ORDER BY TEXT_IDX
        for XML path('')
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,1
    ,''
);

Note, that important part here is to ORDER BY the concatenation values by TEXT_IDX.

perfect. This saves lots of time for me today. Thank you very much — Johnny, May 07 '20 at 06:38

Concatenate different rows data

2 Answers2