You gave too little information.
See
Must Read before posting
Quote:
...
Please follow these guidelines to get quick, apt and meaningful responses :
1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version info) + info on any third party software you use.
You can get his info using the following commands
db2level -> to get db2 version and fixpack level
db2licm -l -> to get the db2 type (WSE, ESE, etc)
...
|
And, it is better(I hope strongly to save my time and to make my work easier)
to supply CREATE TABLE statement and INSERT statement for test data
or "WITH common-table-expression" for test data like I used.
Anyhoiw, this may be an answer
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
Table1(Table1Id , Table1Name , Table1Phone) AS (
VALUES
( 501 , 'AAAAA' , '1111111111' )
, ( 502 , 'BBBBB' , '2222222222' )
, ( 503 , 'CCCCC' , '3333333333' )
)
, Table2(Table2Id , Table2Name) AS (
VALUES
( 1 , 'abc' )
, ( 2 , 'def' )
, ( 3 , 'ghi' )
)
, Table3(Table1Id , Table2Id) AS (
VALUES
( 501 , 1 )
, ( 501 , 2 )
, ( 501 , 3 )
, ( 502 , 1 )
, ( 502 , 2 )
, ( 503 , 1 )
, ( 503 , 3 )
)
SELECT Table1Name
, VARCHAR(
LISTAGG(Table2Name , ', ')
, 20
) AS Table2Name
, MAX(Table1Phone) AS Table1Phone
FROM Table3 t3
INNER JOIN
Table1 t1
ON t1.Table1Id = t3.Table1Id
INNER JOIN
Table2 t2
ON t2.Table2Id = t3.Table2Id
GROUP BY
Table1Name
;
------------------------------------------------------------------------------
TABLE1NAME TABLE2NAME TABLE1PHONE
---------- -------------------- -----------
AAAAA abc, ghi, def 1111111111
BBBBB abc, def 2222222222
CCCCC abc, ghi 3333333333
3 record(s) selected.
dBforums
>
How to display comma seperated in one column
10-31-11, 09:47
Linear Mode
