Group Concatenate

Database

Group Concatenate

I have a data set that looks like the following.

ID Rank Text
a 1 Text1a
a 2 Text2a
a 3 Text3a
b 1 Text1b
b 2 Text2b

 I would like the results to look like this:

ID Text
a Text1aText2aText3a
b Text1bText2b

 I am unsure where to begin. Any help would be appreciated.

1 REPLY
Enthusiast

Re: Group Concatenate

Hi 

Try below.

WITH RECURSIVE TESTING (ID, RANKS, TEXTS)

AS

(

SELECT ID, RANKS, CAST(TEXTS AS VARCHAR(50)) AS TEXTS

FROM TABLE1 WHERE RANKS = 1

UNION ALL

SELECT INDIRECT.ID, INDIRECT.RANKS, DIRECT.TEXTS||INDIRECT.TEXTS AS TEXTS

FROM TESTING DIRECT, TABLE1 INDIRECT

WHERE INDIRECT.ID = DIRECT.ID AND INDIRECT.RANKS = DIRECT.RANKS + 1

)

SELECT ID, TEXTS FROM TESTING

QUALIFY ROW_NUMBER()  OVER(PARTITION BY ID ORDER BY RANKS DESC) = 1

;

Thanking You

Santanu