transposing rows as columns

[oraclepg]

I need to transpose rows as columns.
FOr example consider two tables Cust( cno , cname) and Accts(cno,acctno,amt)

cno is the foreignkey between cust and accts.
cno is the primary key in Cust and acctno is the primary key in Accts.

I want a sql for getting all the acc details for a customer in a single row.
(i.e)
Cust :
Cno cname
--- -----
100 Mark
101 Taylor

Accts:
Cno Acctno AMt
--- ------ ---
100 A909 $5000
100 A209 $10000
101 A200 $20000
101 A400 $30000

I want the results to be displayed as
100 MArk A909 $5000 A202 $10000
101 Taylor A200 $2000 A400 $30000

Can somebody Help !!

 

[johannesd]

You might be able to use something like the following.Take note that you would have to add LEFT JOINS for as many possible different ACCTS a CUST might have (The coding below assumes MAX of three).

Also, the coding below assumes the CUST cannot have more than one of the same ACCT numbers. You also might need to play with the WHERE clause, to make sure you're not excluding any rows or duplicating any rows with the same ACCT no. I hope it works......

SELECT
C.CNO
,A1.ACCTNo ,A1.AMT
,A2.ACCTNo ,A2.AMT
,A3.ACCTNo ,A3.AMT
FROM
CUST as C
LEFT JOIN
ACCTS as A1
ON
C.CNO = A1.CNO
LEFT JOIN
ACCTS as A2
ON
C.CNO = A2.CNO
LEFT JOIN
ACCTS as A3
ON
C.CNO = A3.CNO
WHERE
(A2.Cno IS NULL OR A2.CNO NE A1.CNO)
AND
(A3.Cno IS NULL OR A3.CNO NE A1.CNO)
AND
(A2.Cno IS NULL OR A2.CNO NE A3.CNO);