SQL Server: combining multiple rows into one row
Solution 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
Solution 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
Solution 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.
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.
Solution 4
This is an old question, but as of the release of Microsoft SQL Server 2017 you can now use the STRING_AGG()
function which is much like the GROUP_CONCAT
function in MySQL.
STRING_AGG (Transact-SQL) Documentation
Example
USE AdventureWorks2016
GO
SELECT STRING_AGG (CONVERT(NVARCHAR(max),FirstName), ',') AS csv
FROM Person.Person;
Returns
Syed,Catherine,Kim,Kim,Kim,Hazem
Solution 5
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
Soner Gönül
Software Architect at Akbank. I love solving problems with writing code. Always have, always will. I solve LeetCode and Hackerrank problems, and other algorithms on my Youtube channel. I also stream on Twitch. Tiktok: tiktok.com/@soner_gonul Instagram: instagram.com/sonergonul/
Updated on July 05, 2022Comments
-
Soner Gönül almost 2 years
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?
-
Soner Gönül almost 8 yearsThanks but sorry, my question was for "T-SQL" as you can see. Your answer was also included in Combine Multiple child rows into one row MYSQL by the way.
-
Christian over 6 yearsI have a situation where this will only group two or three rows at max, so this is perfect for me in its simplicity. Seeing as this answer is a few years old, has performance of this method improved?
-
Thomas Gak-Deluen over 6 yearsThere is a very good tutorial here mssqltips.com/sqlservertip/2914/…
-
Jan about 6 yearsIf you are going to run this query from PHP, be sure to declare the @combinedstring as fixed length varchar:
DECLARE @combinedString VARCHAR(255)
-
mehrab habibi about 5 yearsI think use STUFF and FOR XML is the better way to solve the problem
-
Fütemire about 3 yearsI know this was posted in 2011, but as of SQL 2017 you can now use the
STRING_AGG()
function, which is like MySQLGROUP_CONCAT()
is you want to update your answer. ;)