My table looks as follows:

col1,col2
1,a
2,b
3,c
4,d
5,e
6,f

I am trying to write a db2 qyery which would return me following output
a
a-b
a-b-c
a-b-c-d
a-b-c-d-e
a-b-c-d-e-f

I have written a oracle query for the same, which is:
sys_connect_by_path(col2,'-') start with col1=1 connect by col1=prior col1+1

I tried to do it in DB2 but I am not able to get the logic. Can anyone help?

Thanks

This might help you:

http://www.dbforums.com/db2/1076604-how-pivot-dates-into-columns-printthread.html

Andy

I have written a sql but there is a slight prob with it.

What i have is:

WITH parent (col1,path) AS
(SELECT DISTINCT
col1
,cast(col2 as char)
FROM my_table
WHERE col1 = 1
UNION ALL
SELECT C.col1
,cast(P.col2 as char) || '-' || cast(C.col2 as char)
FROM my_table C
,parent P
WHERE P.col1 = C.col1-1
)
SELECT *
FROM parent;

result I am getting is:
1,a
2,a-b
3,b-c
4,c-d
5,d-e
6,e-f

But what I am expecting is:
1,a
2,a-b
3,a-b-c
4,a-b-c-d
5,a-b-c-d-e
6,a-b-c-d-e-f

What DB2 version and OS are you using?

I tried your SQL and got errors. First p.col2 was undefined and then value a-b is too long.

I changed it to this and it works as intended:

WITH parent (col1,path) AS
(SELECT DISTINCT
col1
,col2
FROM mytable
WHERE col1 = 1
UNION ALL
SELECT C.col1
,P.path || '-' ||C.col2
FROM mytable C
,parent P
WHERE P.col1 = C.col1-1
)
SELECT *
FROM parent;


Andy