Can something like this be done with a select 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
STUFF function to a field outside of the function using a join, in order to restrict the resultset and hence concatenated string for each result in an outer resultset.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.
