Looking for a function(s) that will provide something similar to GROUP_CONCAT() or LISTAGG()
Looking for a function(s) that will provide something similar to GROUP_CONCAT() or LISTAGG()
(OP)
Essentially I'm attempting to accomplish concatenating data in a field for records that share the same key in a particular table, as shown below.
I've seen the GROUP_CONCAT(), and LISTAGG() listed on other similar questions, and I am familiar with implementations that would solve the situation below but it seems like PervasiveSQL 13 does not have support for these functions. I am wondering if Pervasive supports anything that I could use to solve my issue.
Either of the following solutions would be acceptable for my use case, but the first one would be more preferable.
OR
I've seen the GROUP_CONCAT(), and LISTAGG() listed on other similar questions, and I am familiar with implementations that would solve the situation below but it seems like PervasiveSQL 13 does not have support for these functions. I am wondering if Pervasive supports anything that I could use to solve my issue.
CODE -->
id colour 1 red 1 blue 2 green 2 red
Either of the following solutions would be acceptable for my use case, but the first one would be more preferable.
CODE -->
id colour 1 red, blue 2 green, red
OR
CODE -->
id colour1 colour2 1 red blue 2 green red
Talk To Other Members
RE: Looking for a function(s) that will provide something similar to GROUP_CONCAT() or LISTAGG()
Something like this:
CODE
create function udfFlattenRow(IN :ID int) returns char(500) as begin declare :result char(500); declare :loopvalue char(10); DECLARE c1 CURSOR FOR SELECT color FROM ttGroup where id = :ID FOR READ ONLY; OPEN c1; BulkLinesLoop: LOOP FETCH NEXT FROM c1 INTO :loopvalue; set :result = :result + ',' + :loopvalue; IF SQLSTATE = '02000' THEN LEAVE BulkLinesLoop; END IF; END LOOP; CLOSE c1; return :result; end;Mirtheil
http://www.mirtheil.com