horizontal display of records

[thijo]

consider a table employee with fields empid, ename and department.
records are

(1,Ram,sales),(2,Abi,Purchase)

consider another table salary with fields empid,month,year,and amount received every month.
records are

(1,january,2007,2500),(1,february,2007,2300),
(1,march,2007,2800),(1,april,2007,2300),
(1,may,2007,2300),(1,june,2007,3300),
(1,july,2007,2300),(1,august,2007,2300),
(1,september,2007,4300),(1,october,2007,2300),
(1,november,2007,4300),(1,december,2007,4300),
(1,january,2008,4300),(1,february,2008,2300),
(1,march,2008,4300),(1,april,2008,2300),
(1,may,2008,2300),(1,june,2007,2300)

now i have to display the total amount recieved by the employee 1 during the period may2007-jan2008 in the format

empname |may2007|june2007|july2007|...........|jan2008|total|
        |       |        |        |           |       |     |
Ram     |2300   |2300    |2300    |...........|4300   |     |
 

[Atli]

Hi.

The result layout you posted is very hard to get out of a database, because you are using data as the column headers.
It's not impossible, but far more trouble to accomplish than it is worth.

You should try not to force to much formatting out of a database. It's job is primarily to store data. Your front-end application should be responsible for re-arranging the data into whatever format you want it to be displayed in.

To get the data you wanted, you should simply have the database return a row for each month for each employee, along with the amount for that month.

+---------+------------+---------+
| empname | date       | ammount |
+---------+------------+---------+
| Abi     | 2007-01-01 |     600 |
| Abi     | 2007-02-01 |    1000 |
| Abi     | 2007-03-01 |     400 |
| Ram     | 2007-01-01 |    2000 |
| Ram     | 2007-02-01 |    1500 |
| Ram     | 2007-03-01 |     500 |
+---------+------------+---------+

To get the total sums as well, you could add a WITH ROLLUP clause to the GROUP BY clause.
Like:

SELECT empname, date, SUM(ammount)
FROM empsalary
GROUP BY empname, date WITH ROLLUP;
 

Which would return these results:

+---------+------------+--------------+
| empname | date       | SUM(ammount) |
+---------+------------+--------------+
| Abi     | 2007-01-01 |          600 |
| Abi     | 2007-02-01 |         1000 |
| Abi     | 2007-03-01 |          400 |
| Abi     | NULL       |         2000 |
| Ram     | 2007-01-01 |         2000 |
| Ram     | 2007-02-01 |         1500 |
| Ram     | 2007-03-01 |          500 |
| Ram     | NULL       |         4000 |
| NULL    | NULL       |         6000 |
+---------+------------+--------------+

There you have the total for each employee added, identifiable by the NULL value in the date field, and the total sum from all row, identifiable by the NULL value in both empname and date fields.