I have three tables in DB They are as follow Emp_ID (Primary Key) | Name ( random values like empName1, empName2..) Mode_id ( Primary Key) | Mode( random values like Mode1, Mode2..) id (Primary Key) | date | mode_id | emp_id Now i want such a query as to the final columns and all look like this : Date empNAme1 empName2 and so on.. 23/03/2011 mode1 mode1 24/03/2011 mode2 mode1 and so on. I think it would be done with cursor within a cursor but am not being able to figure out Thanks
Are you looking for a set number of columns or will they be variable? Your example says date empName1, empName2: 1) Are empName1 and empName2 names of two different employees or first and last name? 2) Your results then say 23/03/2011, mode1, mode2. Those results seem to come from the mode table, not the employee table. Because it is hard to tell what you're trying to do from the example, I'm going to make up something and you tell me if I'm on the right track. I don't need to know if this is what you are doing specifically, just that the output would be the same. EMP_Table (a list of my employees) EMP_ID | NAME 1 | Bob 2 | Doug 3 | Fred Mode_Table (a list of jobs I would have my employees do) MODE_ID | MODE 1 | Sweep 2 | Cashier 3 | Phones Attendance Table (tracks who was in on what day and what their job was that day) ATT_ID | DATE | EMP_ID | MODE_ID 1 | 1/1/11 | 1 | 2 2 | 1/1/11 | 2 | 3 3 | 1/1/11 | 3 | 1 4 | 1/2/11 | 2 | 1 5 | 1/2/11 | 3 | 2 6 | 1/3/11 | 1 | 2 7 | 1/3/11 | 3 | 1 8 | 1/4/11 | 2 | 2 So your results would look like: 1/1/11 Bob Cashier, Doug Phones, Fred Sweep 1/2/11 Doug Sweep, Fred Cashier 1/3/11 Bob Cashier, Fred Sweep 1/4/11 Doug Cashier Does that look like what you are looking for?
Heya. thanks for your time Well, the number of columns would depend upon the number of employees in the table Going by your example, this is what i am hoping to obtain : Dates | Bob | Doug | Fred 1/1/11 | Cashier | Phones | Sweep 1/2/11 | Sweep | Cashier | 1/3/11 | Cashier | Sweep | 1/4/11 | Cashier