I have a Table which looks like

name, year, value
---------------------
john, 1991, 1000
john, 1992, 2000
john, 1993, 3000
jack, 1991, 1500
jack, 1992, 1200
jack, 1993, 1340
mary, 1991, 1250
mary, 1992, 2323
mary, 1993, 8700
and so on

I want to perform a sql query to return results like this:
year, john, Jack, mary ...
1991, 1000, 1500 1250
1992, 2000, 1200, 2323
1993, 3000, 1340, 8700

Any hint will be greatly appreciated.

Hi Niaz,

You can accomplish this by a "pivot" query. Please look at the small testcase that I prepared below:

SQL> desc t1
 Name Null? Type
 --------------- -------- ----------------
 NAME VARCHAR2(10)
 YEAR NUMBER(4)
 VALUE NUMBER(4)

SQL>
SQL> select * from t1;

NAME YEAR VALUE
---------- ---------- ----------
john 1991 1000
john 1992 2000
john 1993 3000
jack 1991 1500
jack 1992 1200
jack 1993 1340
mary 1991 1250
mary 1992 2323
mary 1993 8700

9 rows selected.

SQL> -- now, try out the pivot query
SQL> select year,
  2 max( decode( name, 'john', value, null ) ) "JOHN",
  3 max( decode( name, 'jack', value, null ) ) "JACK",
  4 max( decode( name, 'mary', value, null ) ) "MARY"
  5 from
  6 (
  7 select name, year, value
  8 from t1
  9 )
 10 group by year ;

      YEAR JOHN JACK MARY
---------- ---------- ---------- ----------
      1991 1000 1500 1250
      1992 2000 1200 2323
      1993 3000 1340 8700

SQL>

Hope that helps.

Cheers,
Prat

Thanks Prat.

I also created a similar Query using DECODE and GROUP BY.

Regards

Niaz

Hi,

I have a similar requirement, the only constraint being the fact that I have Strings instead of integers. So, the max() will not work.

Is there a way around that ?

Thanks