SQL Server: combining multiple rows into one row

Question

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ç 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?

Answer 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.

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 

Answer 2

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