SQL Server:将多行合并为一行

I have a SQL query like this;

我有这样的SQL查询;

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

And that's the results;

这是结果;

What I want is; showing in one row (cell) combined all STRINGVALUE's and they are separated with a comma. Like this;

我想要的是什么;显示在一行(细胞)结合所有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

How can I do that?

我怎么做呢?

5 个解决方案

#1

There are several methods.

有几种方法。

If you want just the consolidated string value returned, this is a good quick and easy approach

如果您希望只返回合并的字符串值，这是一种快速而简单的方法

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

SELECT @combinedString as StringValue 

Which will return your combined string.

它会返回组合字符串。

You can also try one of the XML methods e.g.

你也可以尝试的一个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

#2

You can achieve this is to combine For XML Path and STUFF as follows:

您可以实现这一点，即将XML路径和以下内容合并:

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

#3

There's a convenient method for this in MySql called GROUP_CONCAT. An equivalent for SQL Server doesn't exist, but you can write your own using the SQLCLR. Luckily someone already did that for you.

在MySql中有一个方便的方法叫做GROUP_CONCAT。SQL Server不存在类似的函数，但是您可以使用SQLCLR编写自己的函数。幸运的是，已经有人帮你做了。

Your query then turns into this (which btw is a much nicer syntax):

然后你的查询变成了这个(顺便说一句，这是一个更好的语法):

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

But please note that this method is good for at the most 100 rows within a group. Beyond that, you'll have major performance problems. SQLCLR aggregates have to serialize any intermediate results and that quickly piles up to quite a lot of work. Keep this in mind!

但是请注意，这个方法对一个组内最多100行是有好处的。除此之外，你还会遇到重大的性能问题。SQLCLR聚合必须对任何中间结果进行序列化，这将很快积累大量工作。牢记这一点!

Interestingly the FOR XML doesn't suffer from the same problem but instead uses that horrendous syntax.

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

#4

Using MySQL inbuilt function group_concat() will be a good choice for getting the desired result. The syntax will be -

使用MySQL inbuilt function group_concat()将是获得所需结果的良好选择。语法将是-。

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

Before you execute the above command make sure you increase the size of group_concat_max_len else the the whole output may not fit in that cell.

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

To set the value of group_concat_max_len, execute the below command-

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

SET group_concat_max_len = 50000;

You can change the value 50000 accordingly, you increase it to a higher value as required.

您可以相应地更改值50000，您可以根据需要将其增加到更高的值。

#5

I believe for databases which support listagg function, you can do:

我相信对于支持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