A very small post this one, covering a little nugget that I’d almost forgot until it came up trumps again this week; TSQL FOR XML PATH can be a nice solution for concatenation of strings across rows (in a given column).
It’s fairly common to have the need to concatenate column based data, as the following example illustrates:
--Standard concatenation of column values (comma separated, produces multiple rows) SELECT FORENAME + ', ' + SURNAME AS [FULL_NAME] FROM dbo.tblTEST t WHERE t.ID < 4;
However, don’t forget that working through and concatenating row based data, in a particular column, can be achieved simply using the FOR XML PATH construct, just like this:
--Concatenation of row values for a particular column (imagine we wanted comma separated forenames for example) - Provides a single column as structured SELECT CONVERT --Conversion required to an NVARCHAR(VALUE) (MAX depending on string size) - The result will be XML when using FOR XML PATH initially ( NVARCHAR(100) , ( SELECT t.FORENAME + CASE WHEN t.ID < 3 --Don't add a comma after the last value (just for illustration purposes) THEN ', ' ELSE '' END FROM dbo.tblTEST t WHERE t.ID < 4 FOR XML PATH('') --Specify FOR XML PATH using an empty string (we don't want a wrapping element when concatenating strings) ) ) AS [COMMA_SEPARATED_FORENAMES];
Interesting use of FOR XML PATH that’s well worth keeping in mind, it can come in dead handy. Apologies for the Short and sweet post; it’s the order of the day! I’ve managed to pick up the dreaded lurgy so I’m dosed up on medication and drinking a tonne of coffee! Here’s hoping that this post makes sense when I read it later on.
Until the next time, bye for now!