SQL select statement string concatenation

Can something like this be done with a sel开发者_开发问答ect statement:

SELECT col1, concat(col2 + ' ') FROM ....
       GROUP BY col1

I know i can use count(col2) or sum(col2) for integers but is there a function for concatenating if the type is nvarchar or nchar?

In SQL Server, if you want to concatenate across rows, there is no built in function to do this.

I personally like using XML PATH as it seems to perform well, but this will work only in SQL Server 2005 onwards

SELECT
  STUFF(
    (
    SELECT
      ' ' + Description
    FROM dbo.Brands
    FOR XML PATH('')
    ), 1, 1, ''
  ) As concatenated_string

The + operator is used to concatenate strings in T-SQL.

EDIT:

If you wish to aggregate strings over multiple rows this might help.

Using Sql Server there is no built-in aggregate concatenation foncton, I know MySql has one called group_concat.

Sql Server, you, would either have to write your own scaler funcion, or a CLR function to achieve this.

Or you can use a cursor to do this for you, with a table var to return the results. I can provide an example if you like.