SQL Server: 将多行合并为一行

Question

我有这样一个 SQL 查询语句：

SELECT * 
FROM Jira.customfieldvalue
WHERE CUSTOMFIELD = 12534
AND ISSUE = 19602

这就是结果;

我想要的是：将所有STRINGVALUE以逗号分隔的形式合并到一个单元格中显示，如下所示;

SELECT --some process with STRINGVALUE--
FROM Jira.customfieldvalue
WHERE CUSTOMFIELD = 12534
AND ISSUE = 19602

Araç Listesi (C2, K1 vb.Belgeler; yoksa Ruhsat Fotokopileri), Min. 5
araç plakası için İnternet Sorgusu, Son 3 Yıla Ait Onaylı Yıl Sonu
Bilanço + Gelir Tablosu, Son Yıl (Yıl Sonuna ait) Detay Mizanı, İçinde
Bulunduğumuz Yıla ait Ara Dönem Geçici Vergi Beyannamesi, Bayi Yorum
E-Maili, Proforma Fatura

我该怎么做？

- Soner Gönül

---

也就是如何违反第一范式，这在 SQL 中并不容易实现（一种“异味”），因为它涉及生成非关系型结果。相反，应该在报表工具、前端代码等地方实现。 - onedaywhen

Answer 1

有几种方法。

如果你只想返回合并后的字符串值，这是一个好的快速简单的方法。

DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + ', ', '') + stringvalue
FROM jira.customfieldValue
WHERE customfield = 12534
    AND ISSUE = 19602

SELECT @combinedString as StringValue 

这将返回您组合的字符串。

您还可以尝试其中一个XML方法，例如

SELECT DISTINCT Issue, Customfield, StringValues
FROM Jira.customfieldvalue v1
CROSS APPLY ( SELECT StringValues + ',' 
              FROM jira.customfieldvalue v2
              WHERE v2.Customfield = v1.Customfield 
                  AND v2.Issue = v1.issue 
              ORDER BY ID 
                  FOR XML PATH('') )  D ( StringValues )
WHERE customfield = 12534
    AND ISSUE = 19602

- Code Magician

---

这里有一个非常好的教程：https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/ - Thomas Gak-Deluen

如果您要从PHP运行此查询，请确保将@combinedstring声明为固定长度的varchar：DECLARE @combinedString VARCHAR(255)。 - Jan

Answer 2

您可以通过将 For XML Path 和 STUFF 结合使用来实现此操作：

SELECT (STUFF((
        SELECT ', ' + StringValue
        FROM Jira.customfieldvalue
        WHERE CUSTOMFIELD = 12534
        AND ISSUE = 19602
        FOR XML PATH('')
        ), 1, 2, '')
    ) AS StringValue

- Karthikeyan P

Answer 3

这是一个老问题，但自从Microsoft SQL Server 2017发布以来，您现在可以使用STRING_AGG()函数，它类似于MySQL中的GROUP_CONCAT函数。

STRING_AGG (Transact-SQL)文档

示例

USE AdventureWorks2016
GO
SELECT STRING_AGG (CONVERT(NVARCHAR(max),FirstName), ',') AS csv 
FROM Person.Person; 

返回

Syed,Catherine,Kim,Kim,Kim,Hazem

- Fütemire

Answer 4

MySql中有一个方便的方法叫做GROUP_CONCAT，可以实现此功能。虽然SQL Server没有等效的方法，但是您可以使用SQLCLR编写自己的方法。幸运的是，有人已经为您做了这件事。

您的查询变成了以下内容（顺便说一下，这是一种更好的语法）：

SELECT CUSTOMFIELD, ISSUE, dbo.GROUP_CONCAT(STRINGVALUE)
FROM Jira.customfieldvalue
WHERE CUSTOMFIELD = 12534 AND ISSUE = 19602
GROUP BY CUSTOMFIELD, ISSUE

但请注意，此方法最多适用于组内的100行。超出这个范围，您将遇到严重的性能问题。 SQLCLR聚合必须对任何中间结果进行序列化，这很快就会积累大量工作量。请记住这一点！

有趣的是，FOR XML不会遇到同样的问题，而是使用可怕的语法。

- John Leidegren

---

我有一个情况，最多只会分组两到三行，所以这个方法的简单性非常适合我。由于这个答案已经几年了，这种方法的性能有没有得到改进？ - Christian

我知道这篇文章是在2011年发布的，但是从SQL 2017开始，你现在可以使用STRING_AGG()函数了，它就像MySQL中的GROUP_CONCAT()函数一样，如果你想更新你的答案的话。 ;) - Fütemire

Answer 5

我认为对于支持listagg函数的数据库，您可以执行以下操作：

select id, issue, customfield, parentkey, listagg(stingvalue, ',') within group (order by id)
from jira.customfieldvalue
where customfield = 12534 and issue = 19602
group by id, issue, customfield, parentkey

- Liang

Answer 6

CREATE VIEW  [dbo].[ret_vwSalariedForReport]
AS
     WITH temp1 AS (SELECT
     salaried.*,
     operationalUnits.Title as OperationalUnitTitle
FROM
    ret_vwSalaried salaried LEFT JOIN
    prs_operationalUnitFeatures operationalUnitFeatures on salaried.[Guid] = operationalUnitFeatures.[FeatureGuid] LEFT JOIN 
    prs_operationalUnits operationalUnits ON operationalUnits.id = operationalUnitFeatures.OperationalUnitID 
    ), 
temp2 AS (SELECT
    t2.*,
    STUFF ((SELECT ' - ' + t1.OperationalUnitTitle
        FROM
            temp1 t1 
        WHERE t1.[ID] = t2.[ID]  
        For XML PATH('')), 2, 2, '') OperationalUnitTitles from temp1 t2) 
SELECT 
    [Guid],
    ID,
    Title,
    PersonnelNo,
    FirstName,
    LastName,
    FullName,
    Active,
    SSN,
    DeathDate,
    SalariedType,
    OperationalUnitTitles
FROM 
    temp2
GROUP BY 
    [Guid],
    ID,
    Title,
    PersonnelNo,
    FirstName,
    LastName,
    FullName,
    Active,
    SSN,
    DeathDate,
    SalariedType,
    OperationalUnitTitles

- mehrab habibi

---

我认为使用STUFF和FOR XML是解决这个问题的更好方法。 - mehrab habibi

Answer 7

declare @maxColumnCount int=0;
 declare @Query varchar(max)='';
 declare @DynamicColumnName nvarchar(MAX)='';

-- table type variable that store all values of column row no
 DECLARE @TotalRows TABLE( row_count int)
 INSERT INTO @TotalRows (row_count)
 SELECT (ROW_NUMBER() OVER(PARTITION BY InvoiceNo order by InvoiceNo Desc)) as row_no FROM tblExportPartProforma

-- Get the MAX value from @TotalRows table
 set @maxColumnCount= (select max(row_count) from @TotalRows)
 
-- loop to create Dynamic max/case and store it into local variable 
 DECLARE @cnt INT = 1;
 WHILE @cnt <= @maxColumnCount
 BEGIN
   set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= '+cast(@cnt as varchar)+' then InvoiceType end )as InvoiceType'+cast(@cnt as varchar)+''
      set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= '+cast(@cnt as varchar)+' then BankRefno end )as BankRefno'+cast(@cnt as varchar)+''
            set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= '+cast(@cnt as varchar)+' then AmountReceived end )as AmountReceived'+cast(@cnt as varchar)+''

      set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= '+cast(@cnt as varchar)+' then AmountReceivedDate end )as AmountReceivedDate'+cast(@cnt as varchar)+''


   SET @cnt = @cnt + 1;
END;

-- Create dynamic CTE and store it into local variable @query 
  set @Query='
     with CTE_tbl as
     (
       SELECT InvoiceNo,InvoiceType,BankRefno,AmountReceived,AmountReceivedDate,
       ROW_NUMBER() OVER(PARTITION BY InvoiceNo order by InvoiceNo Desc) as row_no
       FROM tblExportPartProforma
      )
  select
     InvoiceNo
     '+@DynamicColumnName+'
     FROM CTE_tbl
     group By InvoiceNo'

-- Execute the Query
 execute (@Query)

- Anand

Answer 8

使用MySQL内置函数group_concat()将是获取所需结果的不错选择。语法如下 -

SELECT group_concat(STRINGVALUE) 
FROM Jira.customfieldvalue
WHERE CUSTOMFIELD = 12534
AND ISSUE = 19602

在执行上述命令之前，请确保增加group_concat_max_len的大小，否则整个输出可能无法适应该单元格。

要设置group_concat_max_len的值，请执行以下命令-

SET group_concat_max_len = 50000;

您可以根据需要将值50000更改为更高的值。

- Rito

---

谢谢，但抱歉，我的问题是关于“T-SQL”的，正如您所看到的。顺便说一下，您的答案也包含在将多个子行合并为一行MYSQL中。 - Soner Gönül