Recursive SQL

Database
Enthusiast

Recursive SQL

Hi

I've the below data format:

c1       c2

1          1

1          2

1          3

2          1

2          2

And, I need the output as below:

c1       c2

1         1,2,3

2         1,2

I tried the following recursive SQL but this query never came back:

WITH RECURSIVE test ( c1,c2 )

AS

(

SELECT c1, CAST(c2 AS CHAR(15))

   FROM ngovind.temp

UNION ALL

SELECT p.c1, CAST(p.c2||','||r.c2 AS CHAR(15))

  FROM ngovind.temp p,

       test r

 WHERE p.c1 = r.c1

)

SELECT c1,CAST(c2 AS CHAR(15))

  FROM test

I'm kind of missing an exit condition, I guess. Any suggestions?

My DDL as below:

CREATE TABLE ngovind.temp

(

c1 INTEGER,

c2 INTEGER

)

INSERT INTO ngovind.temp VALUES (1,1);

INSERT INTO ngovind.temp VALUES (1,2);

INSERT INTO ngovind.temp VALUES (1,3);

INSERT INTO ngovind.temp VALUES (2,1);

INSERT INTO ngovind.temp VALUES (2,2);