I want to concat some rows

Question

I am looking at app data where free text is allowed only 100 characters per DB entry. If it goes over 100 then a new row is created in the DB.

I've looked at this solution and tried to implement it. It's not working as I expect it. How to concatenate text from multiple rows into a single text string in SQL server?

Data Structure:
NAME_ID     NAME    ATXR_SOURCE_ID  ATSY_ID ATXR_DESC   Note
500 Jane    463495.0251 ATN0    Affiliation Add                                                         dfsdfsfdsafdsadfsadfdsafsadsfdasfd
500 Jane    463495.0251 ATN0    Affiliation Add                                                         asdfdsfdsafdafdasdfsadfsadfsadfadfsa
500 Jane    463495.0251 ATN0    RITM0072237                                                             Update to record
500 Jane    463495.0251 ATN0    RITM0074636                                                             Script update
500 Jane    463495.0251 ATN0    RITM0079507                                                             Services updated




SELECT DISTINCT ST2.NAME_ID, ST2.NAME, ST2.ATXR_DESC, ST2.Note,
    SUBSTRING(
        (
            SELECT NAME_ID, NAME, ATXR_SOURCE_ID, ATSY_ID, ATXR_DESC, ','+ Note  AS [text()]
            FROM #Notes ST1
            WHERE ST1.ATXR_DESC = ST2.ATSY_ID
            ORDER BY ST1.Note
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM #Notes ST2

Actual:
NAME_ID     NAME    DESC    Note    Students
500 Jane    Affiliation Add                                                         dfsdfsfdsafdsadfsadfdsafsadsfdasfd  NULL
500 Jane    Affiliation Add                                                         asdfdsfdsafdafdasdfsadfsadfsadfadfsa    NULL
500 Jane    TicketA Update to record    NULL
500 Jane    TicketB Script update   NULL
500 Jane    TicketC Services updated    NULL

Expected (For rows 1 and 2 I want the Notes to be "concatenated"):

NAME_ID     NAME    DESC    Note    Students
500 Jane    Affiliation Add                                                         dfsdfsfdsafdsadfsadfdsafsadsfdasfdasdfdsfdsafdafdasdfsadfsadfsadfadfsa  NULL
500 Jane    TicketA Update to record    NULL
500 Jane    TicketB Script update   NULL
500 Jane    TicketC Services updated    NULL

Can you try aligning your data please so that it's readable? I didn't want to guess what it was supposed to be. — Thom A, Sep 04 '19 at 18:09
On a different note, the normal way to remove the first character from a `(n)varchar` in SQL Server is with `STUFF`: `STUFF({varchar expression},1,1,'')`. `SUBSTRING` can truncate your value if it's 3rd parameter isn't long enough, `STUFF` won't cause that "feature". — Thom A, Sep 04 '19 at 18:15
Looks like a typo in your WHERE clause. If you correctly follow the answer in the linked question, you would compare the **same** columns in `ST1` and `ST2`. And remember it's ok to compare on more than one column, which is what you probably really want. — Tab Alleman, Sep 04 '19 at 18:17

droebi · Answer 1 · 2019-09-05T10:20:30.933

If you use SQL Server 2017+ you can use the SRING_AGG() method:

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

SELECT t.NAME_ID,
  t.NAME,
  t.ATXR_DESC,
  STRING_AGG(t.Note,'') AS Note
FROM <TABLE> AS t
GROUP BY t.NAME_ID,
    t.NAME,
    t.ATXR_DESC

-- result:
--NAME_ID   NAME    ATXR_DESC           Note
--500       Jane    Affiliation Add     asdfdsfdsafdafdasdfsadfsadfsadfadfsadfsdfsfdsafdsadfsadfdsafsadsfdasfd
--500       Jane    RITM0072237         Update to record
--500       Jane    RITM0074636         Script update
--500       Jane    RITM0079507         Services updated

Visit following post: How to concatenate text from multiple rows into a single text string in SQL server?

If you use an older SQL Server version, you can implement an assembly for string concatenation: Does T-SQL have an aggregate function to concatenate strings?

I want to concat some rows

1 Answers1