Grouping in Oracle

Hi Guys,

I am getting back to Oracle after a long gap of 4 yrs. I have a basic SQL question. Here it is.

I have a query that joins multiple tables and the result is as follows

Name MyCol1 MyCol2 MyCol3
---------------------------------------
ABC 1 - -
ABC - 2 -
ABC - - 3
PQR 2 - -
PQR - 5 -
PQR - - 4
XYZ 6 - -
XYZ - 1 -
XYZ - - 2


The table structures are as follows

Table1 (Parent table)
------
Name

Table2 (Child table)
------
Name (References Name from Table1)
ColName (Which could be MyCol1/MyCol2/MyCol3)
ColValue (A corresponding value for ColName)

This is how my query is ...

SELECT t1.Name,
DECODE (t2.ColName, 'MyCol1', t2.ColValue) MyCol1,
DECODE (t2.ColName, 'MyCol2', t2.ColValue) MyCol2,
DECODE (t2.ColName, 'MyCol3', t2.ColValue) MyCol3
FROM Table1 t1, Table2 t2
WHERE t1.Name = t2.Name

What would be easiest way to get my result look like follows...

Name MyCol1 MyCol2 MyCol3
---------------------------------------
ABC 1 2 3
PQR 2 5 4
XYZ 6 1 2

Is it possible to achieve this using a query and not writing a PL/SQL script?

Please advice.

Regards,
OracleGreenHorn

The simplest is :
SELECT t1.Name,
MAX(DECODE (t2.ColName, 'MyCol1', t2.ColValue)) MyCol1,
MAX(DECODE (t2.ColName, 'MyCol2', t2.ColValue)) MyCol2,
MAX(DECODE (t2.ColName, 'MyCol3', t2.ColValue)) MyCol3
FROM Table1 t1, Table2 t2
WHERE t1.Name = t2.Name
group by t1.Name

The warning is that, it assumes you will not have duplicates (ie the following situation would never occur). If it could, you'll have to decide what you want your SQL to do in those circumstances (eg sum up the values, show the MAX or MIN, raise an exception....)

ABC 1 - -
ABC - 2 -
ABC - 3 -
ABC - - 4

Gamyers,

I appreciate your help. It worked for me and it's good enough for the time being.

Thanks again,
OracleGreenHorn