[sql-server] SQL Server: combining multiple rows into one row

I have a SQL query like this;

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;

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ç plakasi için Internet Sorgusu, Son 3 Yila Ait Onayli Yil Sonu
Bilanço + Gelir Tablosu, Son Yil (Yil Sonuna ait) Detay Mizani, Içinde
Bulundugumuz Yila ait Ara Dönem Geçici Vergi Beyannamesi, Bayi Yorum
E-Maili, Proforma Fatura

How can I do that?

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.

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!

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

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

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

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

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

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.

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

SET group_concat_max_len = 50000;

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

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

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