posted 12/22/2010 by timmurphy - Views: [2547]
I was looking for a way to combine text from multiple rows into a single text column. There are several ways I found to do this but here is a way using FOR XML PATH.
First the reason: My source data comes from a transactional DB that users enter text descriptions in multiple transactions. Here is mockup:
Text
TextFamily
TextGroup
TextSequence
This is a sample text line.
C
1
This is another line which
A
is followed by the next line that
2
may roll over to the next, but
3
should be read as a single
4
item of text, etc.
5
This belongs to TextFamily B
B
as does this row of text.
This is the last line of text
that belongs to TextFamily A
If you study the data you see there are 7 transactions (rows) for TextFamily A, 1 for TextFamily C and 2 belong to TextFamily B. The TextGroup keeps the order of entry and the TextSequence keeps the order within the TextGroup.
Here is the T-SQL I used:
SELECT t1.TextFamily AS TextFamily
,(SELECT RTRIM(t2.TextLine) + ' '
FROM TextTable t2
WHERE t1.TextFamily = t2.TextFamily
ORDER BY TextGroup, TextSequence
FOR XML PATH('')) AS FamilyTextDescription
FROM TextTable t1
GROUP BY TextFamily
The result set looks like this which is what I wanted for reporting.
FamilyTextDescription
This is a line which is followed by the next line that may roll over to the next, but should be read as a single item of text, etc. This is the last line that belongs to TextFamily A
This belongs to TextFamily B as does this text.
This is a sample line of text.
Granted, this may be an unusual example using FOR XML PATH but it might come in handy if you ever pull from a system that keeps descriptions this way.
You blogged this today, and I needed it today! Great timing!
Glad you could use it, David.
Hi,
This can create a problem when there are some HTMP markup chars in string column.
If we do it this way we can avoide the problem of HTML markup chars.
FOR XML PATH(''),type).value('.','nvarchar(max)') AS FamilyTextDescription
- Chintak
Thanks for pointing that out Chintak.