0

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)?

Community
  • 1
  • 1
Drux
  • 11,992
  • 13
  • 66
  • 116

2 Answers2

3

Rather than xml path why not do it like this?

DECLARE @Cols VARCHAR(8000) 
SELECT @Cols = COALESCE(@Cols + ', ', '') + 
    ISNULL(col, 'N/A')
FROM t1
Insert into t2 values(@Cols);
TheProvost
  • 1,832
  • 2
  • 16
  • 41
  • I see this more often when concatenating string, that people use `COALESCE()` with just 2 arguments. Is there a reason to use `COALESCE()` here rather than `ISNULL()`? I've used `ISNULL()` in these cases and didn't have problems with it evaluating `@Cols + ', '` only once or anything. – asontu Nov 04 '14 at 09:50
  • +1 VG but I still can't get the `ISNULL` part to work (result is `null` instead of `N/A`). I am using a subquery that starts with `SELECT ... AS col ...` instead of `t2`. Any further ideas? – Drux Nov 04 '14 at 09:50
  • Show me the exact query. – TheProvost Nov 04 '14 at 10:14
2

You need to cast it back to an nvarchar() before inserting. I use this method, deletes the first separator as well and as I'm doing the , type part, it handles entities correctly.

insert into t2
select stuff((
    select ', ' + col from t1
    for xml path(''), type
).value('.', 'nvarchar(80)'), 1, 2, '')

So you concatenate all col with prepending comma+space as an xml-object. Then you take the .value() of child with xquery-path . which means "take the child we are at, don't traverse anywhere". You cast it as an nvarchar(80) and replace a substring starting at position 1 and length 2 with an empty string ''. So the 2 should be replaced with however long your separator is.

asontu
  • 4,548
  • 1
  • 21
  • 29