SQL HELP

Renu Sharma

SQL HELP
Hello List

I need help to write this sql
Table name is TEST1
Table structure is :
id smallint,
idx smallint,
Detail varchar(30)

Data is :
id , idx , detail
1 , 0 , 'Mike Angel'
1 , 1 , '323 apt '
1 , 2 , 'Main Street'
1 , 3 , 'NY'
1 , 4 , 'NY'
2 , 0 , 'David Ester'
2 , 1 , '12 Edgemere Street'
2 , 2 , 'Princeton'
2 , 3 , 'NJ'
3 , 0 , 'RS'
3 , 1 , ' '

I want the result as

1, 'Mike Angel 323 apt Main street NY NY'
2, 'David Ester 12 Edgemere Street Princeton NJ'
3, 'RS'


Thanks in advance

Renu



**************Start the year off right. Easy ways to stay in shape.
http://body.aol.com/fitness/winter-exercise?NCID=aolcmp00300000002489

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

James Campbell

Re: SQL HELP
(in response to Renu Sharma)
Multiple LEFT OUTER JOINS from TEST1 to TEST1, with a
COALESCE on detail to convert NULLs (when there isn't a
right side of the join) to a blank. I'll leave the details
to you.

James Campbell



> Hello List
>
> I need help to write this sql
> Table name is TEST1
> Table structure is :
> id smallint,
> idx smallint,
> Detail varchar(30)
>
> Data is :
> id , idx , detail
> 1 , 0 , 'Mike Angel'
> 1 , 1 , '323 apt '
> 1 , 2 , 'Main Street'
> 1 , 3 , 'NY'
> 1 , 4 , 'NY'
> 2 , 0 , 'David Ester'
> 2 , 1 , '12 Edgemere Street'
> 2 , 2 , 'Princeton'
> 2 , 3 , 'NJ'
> 3 , 0 , 'RS'
> 3 , 1 , ' '
>
> I want the result as
>
> 1, 'Mike Angel 323 apt Main street NY NY'
> 2, 'David Ester 12 Edgemere Street Princeton NJ'
> 3, 'RS'
>
>
> Thanks in advance
>
> Renu
>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms