Change column name generated by FOR XML PATH in SQL Server

owfi6suc 于 2023-03-17 发布在 SQL Server

关注(0)|答案(3)|浏览(179)

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.

sql-server

来源：https://stackoverflow.com/questions/68879852/change-column-name-generated-by-for-xml-path-in-sql-server

3条答案

按热度按时间

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

赞(0）回复(0）举报 2023-03-17

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

赞(0）回复(0）举报 2023-03-17

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

赞(0）回复(0）举报 2023-03-17

我来回答

Change column name generated by FOR XML PATH in SQL Server

3条答案

相关问题

热门标签

最新问答