ganeshprasanna
New User
Joined: 21 Feb 2009
Posts: 36
Location: South Portland, Maine
|
|
|
|
SQL query :
| Code: |
SELECT A,B,C FROM TABLE
WHERE A=6990 |
Result set:
| Code: |
A B C
DECIMAL(14) CHAR(1) SMALLINT(2)
-PKEY4--------- -PKEY2----- -PKEY3-----
+6990 B 1
+6990 B 2
+6990 B 4
+6990 B 5
+6990 C 1
+6990 C 2
+6990 C 3
+6990 C 4
+6990 C 5
+6990 C 6
+6990 C 7
|
PS: The result set (no of rows ) returned might vary with the value of A..
My requirement is the query output should be displayed as
| Code: |
A B C
+6990 B 1 2 4 5
+6990 C 1 2 3 4 5 6 7
|
Can this formatting be done in the query itself?
Help on this would be appreciated ..
Thanks,
Ganeshprasanna |
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
DB2 version ?
Using recursive SQL (this works as long as C is unique within cola,colB)
| Code: |
with cte (cnt,colA, colB,colC ,outp) as
( select 1, colA, colB, min(colC), cast(char(min(colC)) as varchar(200))
from tab1
where colA = +6990
group by colA,colB
union all
select a.cnt + 1 , a.colA, a.colB, b.colC, a.outp || char(b.colC)
from cte A , tab1 B
where a.colA = b.colA and a.colB = b.colB
and b.colC = (select min(colC) from tab1 C where c.colC > a.colC)
and a.cnt < 100
)
select colA,colB,outp from cte A
where cnt = (select max(cnt) from cte B where a.colA = b.colA and a.colB = b.colB) |
Using XMLFunctions
| Code: |
select colA, colB
, replace(replace(cast(xml2clob(xmlagg(xmlelement(name X ,colC))) as varchar(200) ),'<X>',' ') ,'</X>','')
from tab1
group by colA,colB |
|
|
ganeshprasanna
New User
Joined: 21 Feb 2009
Posts: 36
Location: South Portland, Maine
|
|
|
|
DB2 version - 9
Worked like a charm, GUYC. 
Any pointers to learning recursive SQL usage. Examples or illustrations would be helpful.
Thanks,
Ganeshprasanna |
|
