CODEWITHSUNDEEP
sql-serversql-server-2008string-concatenation
Rasmus Remmer Bielidt
Rasmus Remmer Bielidt

Reputation: 157

Concatenation of multiple rows into one string

This is my first question asked here at stackoverflow, so bear with me please :)

I asked it in another thread as it was related to that question, but was directed to ask in a new question thread so here it is.

I have a table with note-texts with each line holding a max of 256 characters, these are sequenced by a field "linenumber" but I am not sure that the order of linenumber in the database is in sequence. How would I re-design this query to ensure sequencing by linenumber for each notesrecid?

On the table NOTES_V there is a field called "linenumber" - I tried ordering the BA aliased by notesrecid then linenumber, but ordering is not allowed within a subquery.

I realize that the outcome in extreme and highly unlikely cases may overflow 8000 characters of varchar, but is that a problem or will it "just" be truncated to the 8000 character max?

SELECT A.DATASET, A.NOTESRECID, LEFT(A.NOTETXT,LEN(A.NOTETXT)-2) AS "NOTETXT", LEN(A.NOTETXT)-2 AS "#CHARS"
FROM (SELECT DISTINCT BB.DATASET, BB.NOTESRECID,
        (SELECT BA.TXT+', ' AS [text()]
         FROM NOTES_V BA
         WHERE BA.DATASET=BB.DATASET AND BA.NOTESRECID=BB.NOTESRECID
         ORDER BY BA.DATASET, BA.NOTESRECID
         FOR XML PATH (''))  [NOTETXT]
  FROM NOTES_V BB) A

The method written below works great, but I ran into a problem with CHAR(7) not being serializable, so my slightly re-written query is below replacing CHAR(7) with a blank space.

SELECT A.DATASET, A.NOTESRECID, A.NOTETXT, LEN(A.NOTETXT) AS "#CHARS"
FROM (SELECT BB.DATASET, BB.NOTESRECID,
      stuff((SELECT REPLACE(BA.TXT,CHAR(7),' ')+', ' AS [text()]
             FROM NOTES_V BA
             WHERE BA.DATASET=BB.DATASET AND BA.NOTESRECID=BB.NOTESRECID
             ORDER BY BA.DATASET, BA.NOTESRECID, BA.linenumber
             FOR XML PATH ('A'),type).value('.','nvarchar(max)'),1,2,'') [NOTETXT]
             FROM NOTES_V BB
             GROUP BY BB.DATASET, BB.NOTESRECID) A

Thank you for the help, much appreciated.

Upvotes: 1

Views: 1157

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

SELECT A.DATASET, A.NOTESRECID, A.NOTETXT, LEN(A.NOTETXT) AS "#CHARS"
FROM (SELECT BB.DATASET, BB.NOTESRECID,
        stuff((SELECT BA.TXT+', ' AS [text()]
         FROM NOTES_V BA
         WHERE BA.DATASET=BB.DATASET AND BA.NOTESRECID=BB.NOTESRECID
         ORDER BY BA.DATASET, BA.NOTESRECID, BA.linenumber
         FOR XML PATH ('A'),type).value('.','nvarchar(max)'),1,2,'') [NOTETXT]
  FROM NOTES_V BB
  GROUP BY BB.DATASET, BB.NOTESRECID) A

Notes:

  • STUFF is faster than LEFT or SUBSTRING
  • If your NOTES_V.TXT data contains characters that will become XML entities, you will get some unexpected text. Using FOR XML, TYPE and extracting the value from it ensures this does not happen
  • I prefer GROUP BY because it preceeds DISTINCT in terms of logical flow

Upvotes: 1

Related Questions