Haw to write query to get result in single line in DB2

[MKMK]

Hi,

I would like a ask a question if it possible to get result with query in a single line. We have db2 database.

I have two tables

Matno Name
10 Jim
20 Ken
30 Jane


Matno username
10 u0500j2
10 o0600k2
20 i0500a9
30 o900j3

Result must look like this

Matno Name Username1, Username2,...
10 Jim u0500j2 o0600k2
20 Ken i0500a9
...

Is it possible to do that, please advice me, best regards
and thanks in advance, K

[redz]

hi MKMK,
im not so sure but try this sample

select a.matno,a.`name`,a.username1,b.username2 from
(select a.matno as matno,a.name,b.username as username1,b.username as username2 from
(select * from tbl1) as a
LEFT JOIN
(select * from tbl2) as b on a.matno = b.matno) as a

left join

(select a.matno as matno,a.name,b.username as username1,b.username as username2 from
(select * from tbl1) as a
LEFT JOIN
(select * from tbl2) as b on a.matno = b.matno) as b on a.matno=b.matno where a.username1<>b.username2 LIMIT 1

[Exequiel]

Try to visit aand read this URLs , , it will solve your question. . .http://www.w3schools.com/sql/sql_join_inner.asp

http://www.w3schools.com/sql/sql_join.asp

[redz]

here's the final query..

select a.matno,a.`name`,a.username1 as Username1,case when a.username2=b.username1 then '' else b.username1 end as Username2 from
(select a.matno as matno,a.name,b.username as username1,b.username as username2 from
(select * from tbl1) as a
LEFT JOIN
(select * from tbl2) as b on a.matno = b.matno) as a

left join

(select a.matno as matno,a.name,b.username as username1,b.username as username2 from
(select * from tbl1) as a
LEFT JOIN
(select * from tbl2) as b on a.matno = b.matno) as b on a.matno=b.matno group by a.name order by matno

[redz]

The Result

10 Jim u0500j2 o0600k2
20 Ken i0500a9
30 Jane o900j3

[MKMK]

Hi,

Tanky very much to replay. I tried this but it return a mistake for field a.`name`. When I correct it said
for field a.username1 that is not valid in the context where it is used ;-(.

I must build a query that in one tabele is id from workers and another every worker have many usernames.

Thanks in advance, mkmk

[redz]

hi MKMK,
i used that query in MYSQL,
what type database server are you using?
here's the sample of my table according to your needs.

tbl1...

fields type length
matno int 10
name varchar 40

tbl2...
matno int 10
username varchar 40

[redz]

the field "name" is a reserved word in MYSQL but it never return an
error to me..

sometimes you have to checked what type of database server you are going to used. It unexpectedly returns an error when you used some reserved words.. It is only occurs in the name of the fields

[MKMK]

Helou,

Thanks for replay and I am sory for late response.

I am using db2 database. I will try to write your query
that my base will recognize it, I hope it will work. I let you know.

Thanks in advance, K

[Anas Mosaad]

You can also use LISTAGG to aggregate username in one single field and separate them using a separator (i.e. comma or semi-colon).
This will overcome the limitation of predefined columns. You can have arbitrary number of usernames but all represented in one single column. In your application, you may split the values using the predefined separator.