Concatenation of multiple rows into one string

Question

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.

In SQL 2008 you could use varchar(max). It has no character limit. — tobias86, Commented Apr 6, 2011 at 8:45
You mention that you have tried ordering by notesrecid, but nothing about what this column is. Could you please add table structure with some explanation of the columns? — Andreas Ågren, Commented Apr 6, 2011 at 8:57

RichardTheKiwi · Accepted Answer · 2011-04-06 09:17:24Z

1

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

edited Apr 6, 2011 at 9:17

answered Apr 6, 2011 at 9:09

RichardTheKiwi

108k28 gold badges203 silver badges266 bronze badges

Thanks for the help, appreciate it. However, I am getting an error returned: "FOR XML could not serialize the data for node 'text()' because it contains a character (0x0007) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive." - I am not sure how to proceed around this error - any ideas?
– Rasmus Remmer Bielidt
Commented Apr 6, 2011 at 11:23

Add a comment |

Collectives™ on Stack Overflow

Concatenation of multiple rows into one string

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related