Change column name generated by FOR XML PATH in SQL Server

owfi6suc  于 2023-03-17  发布在  SQL Server
关注(0)|答案(3)|浏览(180)

I am using the FOR XML PATH function in order to concatenate multiple columns into a single row. However, I can't figure out how to alias the column name of the resulting table.

Here is the SQL query:

SELECT Comment
FROM Comments
WHERE ID = 1006
FOR XML PATH('')

I have tried the following two methods which generate an error message:

SELECT Comment
FROM Comments
WHERE ID = 1006
FOR XML PATH('') [Comment_Agg];

SELECT * AS Comment_Agg
FROM
    (SELECT Comment
     FROM Comments
     WHERE ID = 1006
     FOR XML PATH(''));

FYI, I am using SSMS 18.

lztngnrs

lztngnrs1#

I googled more and found the resolution as follows:

SELECT SUBSTRING(
(
SELECT Comment
FROM Comments
WHERE ID = 1006
FOR XML PATH('')
),1,999999) AS Comment_Agg
oxosxuxt

oxosxuxt2#

To use FOR XML PATH to concatenate, the column needs to be unnamed. If you don't want a separator, you can just add + ''

SELECT
 (SELECT Comment + ''   -- causes the column to be unnamed
  FROM Comments
  WHERE ID = 1006
  FOR XML PATH(''), TYPE
 ).value('text()[1]','nvarchar(max)')  -- prevents XML escaping
svgewumm

svgewumm3#

You can use CTE:

with Comment_Agg (your_field_name) as (
  select 
      Comment 
  from
      Comments
  where
      ID = 1006
for xml path(''))
select * from Comment_Agg

相关问题