SQLTeam.com | Weblogs | Forums

Collapse rows into one


Raked my brain for an hour and I cant get this going. Given the following data I want to flatten it to one row and get the following

--"ሀ": "ha", "ሁ": "hhu", "ሂ": "hhi"

declare @unicodeblock table(unicodevalue nchar(1), syllable varchar(10))

insert into @unicodeblock
select N'ሀ', 'ha' union
select N'ሁ', 'hhu' union
select N'ሂ', 'hhi' 


ok got it finally!

SELECT    distinct 
       SELECT  ', ' + concat('"', unicodevalue, '":"', syllable, '"')
       FROM @unicodeblock t2   
       WHERE t2.unicodevalue = t1.unicodevalue   
       FOR XML PATH('')  
FROM @unicodeblock t1  
1 Like

What version of SQL Server are you using?

Microsoft SQL Server 2016

Dang. I was hoping you'd say 2017 or higher. String_Agg works a real treat there.

1 Like

i can upgrade :grinning_face_with_smiling_eyes:

If that's true, then...

 SELECT Result = STRING_AGG(CONCAT('"',unicodevalue,'":"',syllable,'"'),',')
                 WITHIN GROUP (ORDER BY unicodevalue,syllable)
   FROM @unicodeblock

If you don't care about any given order within the result, you can leave out the entire WITHIN GROUP clause.