考虑一个包含名称的数据库表,其中有三行:

Peter
Paul
Mary

有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?


当前回答

对于其他答案,阅读答案的人必须知道特定的域表,例如车辆或学生。必须创建该表并用数据填充该表以测试解决方案。

下面是一个使用SQL Server“Information_Schema.Columns”表的示例。通过使用此解决方案,不需要创建表或添加数据。此示例为数据库中的所有表创建一个逗号分隔的列名列表。

SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME 

其他回答

SQL Server中尚未通过XML data()命令显示的一个方法是:

假设一个名为NameList的表有一列名为FName,

SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')

返回:

"Peter, Paul, Mary, "

只需处理额外的逗号。

正如@NReilingh的注释所采用的,您可以使用以下方法删除尾随逗号。假设表和列名相同:

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands

一个现成的解决方案,没有额外的逗号:

select substring(
        (select ', '+Name AS 'data()' from Names for xml path(''))
       ,3, 255) as "MyList"

空列表将导致NULL值。通常,您会将列表插入到表列或程序变量中:根据需要调整最大长度255。

(迪瓦卡尔和延斯·弗兰森提供了很好的答案,但需要改进。)

在Chris Shaffer的回答之上:

如果您的数据可能会重复,例如

Tom
Ali
John
Ali
Tom
Mike

而不是汤姆、阿里、约翰、阿里、汤姆、迈克

您可以使用DISTINCT避免重复,并让Tom、Ali、John、Mike:

DECLARE @Names VARCHAR(8000)
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names

此方法仅适用于Teradata Aster数据库,因为它使用NPATH函数。

再次,我们有桌上学生

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

然后使用NPATH,只需一次SELECT:

SELECT * FROM npath(
  ON Students
  PARTITION BY SubjectID
  ORDER BY StudentName
  MODE(nonoverlapping)
  PATTERN('A*')
  SYMBOLS(
    'true' as A
  )
  RESULT(
    FIRST(SubjectID of A) as SubjectID,
    ACCUMULATE(StudentName of A) as StudentName
  )
);

结果:

SubjectID       StudentName
----------      -------------
1               [John, Mary, Sam]
2               [Alaina, Edward]

下面是一个使用“基本循环”和“rownum”实现给定场景的简单PL/SQL过程

表定义

CREATE TABLE "NAMES" ("NAME" VARCHAR2(10 BYTE))) ;

让我们将值插入此表

INSERT INTO NAMES VALUES('PETER');
INSERT INTO NAMES VALUES('PAUL');
INSERT INTO NAMES VALUES('MARY');

程序从这里开始

DECLARE 

MAXNUM INTEGER;
CNTR INTEGER := 1;
C_NAME NAMES.NAME%TYPE;
NSTR VARCHAR2(50);

BEGIN

SELECT MAX(ROWNUM) INTO MAXNUM FROM NAMES;

LOOP

SELECT NAME INTO  C_NAME FROM 
(SELECT ROWNUM RW, NAME FROM NAMES ) P WHERE P.RW = CNTR;

NSTR := NSTR ||','||C_NAME;
CNTR := CNTR + 1;
EXIT WHEN CNTR > MAXNUM;

END LOOP;

dbms_output.put_line(SUBSTR(NSTR,2));

END;

后果

PETER,PAUL,MARY