使用分组将多行连接成单个文本字符串 [英] Concatenate many rows into a single text string with grouping

问题描述

我有下表:tblFile

I have the following table: tblFile

我想要的输出:

我将多行连接成一个文本字符串；但是，我无法正确分组.由于代码现在它只会为 FileNameString 字段中的每个记录显示:AAA,BBB,CCC,DDD,EEE,FFF

I am Concatenating many rows into a single text string; however, I cannot get the grouping correct. As the code is now it will just display for each record in the FileNameString field: AAA,BBB,CCC,DDD,EEE,FFF

关于分组的任何建议！

SELECT FileID, Stuff(
(SELECT     N', ' + CONVERT(Varchar, FileName) 
FROM         tblFile  FOR XML PATH(''),TYPE )
.value('text()[1]','nvarchar(max)'),1,2,N'')AS FileNameString 
From tblFile
GROUP BY FileID

推荐答案

试试这个 -

SELECT DISTINCT
      fileid
    , STUFF((
        SELECT N', ' + CAST([filename] AS VARCHAR(255))
        FROM tblFile f2
        WHERE f1.fileid = f2.fileid ---- string with grouping by fileid
        FOR XML PATH (''), TYPE), 1, 2, '') AS FileNameString
FROM tblFile f1

这篇关于使用分组将多行连接成单个文本字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！