SQLTeam.com | Weblogs | Forums

Collapse rows into one

#1

Hello,

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'

Thanks!

#2

ok got it finally!

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

What version of SQL Server are you using?

#4

Microsoft SQL Server 2016

#5

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

1 Like
#6

i can upgrade :grinning_face_with_smiling_eyes:

#7

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.

2 Likes