SQL for concatenating strings/rows into one string/row? (How to use FOR XML PATH with INSERT?)



I am concatenating several rows/strings in an table (on Microsoft SQL Server 2010) into a string by using a method as suggested here:



SELECT ',' + col FROM t1 FOR XML PATH('')


However, if I try to insert the resulting string as (single) row into another table like so:



INSERT INTO t2
SELECT ', ' + col FROM t1 FOR XML PATH('')


I receive this error message:



The FOR XML clause is not allowed in a INSERT statement.


t2 currently has a single column of type NVARCHAR(80). How can I overcome this problem, i.e. how can I collapse a table t1 with many rows into a table t2 with row that concatenates all the strings from t1 (with commas)?


No comments:

Post a Comment