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.

SELECT [year],
       SUM(CASE WHEN [name]='john' THEN [value] ELSE 0 END) AS john,
       SUM(CASE WHEN [name]='jack' THEN [value] ELSE 0 END) AS jack,
       SUM(CASE WHEN [name]='mary' THEN [value] ELSE 0 END) AS mary
FROM yourTable
GROUP BY [year]
ORDER BY [year]

You will have to implement self join on the table.

Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team