STUFF 函数，用于在 SQL Server 中以逗号分隔显示多列

问题描述

我需要在输出中显示以逗号分隔的表格列(假设为 2 列)值

I need to display a table column (assume 2 columns) values with comma-separated in the output

col1    Col2
================
xyz       abc
pqr       uvw

需要输出:

Col1      Col2
========  =======
xyz,pqr    abc,uvw

我尝试了以下查询，但不确定如何将 col2 包含到以下查询中并将其设为单个查询:

I tried below query but not sure how to include col2 to the below query and make it as a single query:

SELECT
    STUFF((SELECT DISTINCT ',' + CAST(t.Col1 AS VARCHAR(MAX))
           FROM Table_1 t
           FOR XML PATH('')), 1, 1, '')

推荐答案

SQL Fiddle

MS SQL Server 2017 架构设置:

create table MyTable(ColA varchar(max),ColB varchar(max))
INSERT INTO MyTable(ColA,ColB)VALUES('xyz','abc')
INSERT INTO MyTable(ColA,ColB)VALUES('pqr','uvw')

查询 1:

SELECT STUFF((select  ',' + cast(t.ColA as varchar(max))
from MyTable t
for xml path ('')
), 1, 1, '') AS ColA,
STUFF((select  ',' + cast(t.ColB as varchar(max))
from MyTable t
for xml path ('')
), 1, 1, '') AS ColB

查询 2:

 SELECT
       STRING_AGG(colA, ',') AS colA,
       STRING_AGG(colB, ',') AS colB
    FROM MyTable

结果:

|    ColA |    ColB |
|---------|---------|
| xyz,pqr | abc,uvw |

这篇关于STUFF 函数，用于在 SQL Server 中以逗号分隔显示多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！