Information from two rows from a query into 1 row

I have 3 database tables:

Workoutput which defines the work done by employees. employeeinfo which defines relationships for each person. The three important fields are child, parent and relation. A person can be l开发者_C百科isted a couple of times with different relationships i.e,

child  parent  relation
12      3         2  
12      43        4

With the relation referring to whether the parent is mentor or manager

And finally a standard employee table which has comprehensive listings about the employee id, name, login etc.

I would like to have a results table which defines all the people above that one worker on a daily basis, but have it defined in one row on a daily basis. At the moment I can only define them on separate days with the following query.

SELECT workoutput.Day, workoutput.Employee, employee.name
FROM workoutput INNER JOIN
        employeeInfo ON workoutput.ID = [employeeinfo].son INNER JOIN
        [employee] ON [employeeInfo].parent = [employee].id INNER JOIN
        [employee] AS [employee1] ON workoutput.[Employee ID] = [employee1].id

This returns the relationships, but there will be two rows for each day, as the majority of people will have two people above them (Mentor and Manager).

29/03/2010 00:00:00 Employee1   Manager 3
29/03/2010 00:00:00 Employee1   Mentor 1

and I would like

29/03/2010 00:00:00 Employee1    Mentor 1  Manager3

I also have another table at my disposal, which define the relationships it has two rows, id and type of relation, with id referring to a relation id defined in the employeeinfo table.

Is that possible?

Thanks

---

Is there a maximum number of parents for a child (i.e. relations)? And is there one per relation?

If so, you can do this with a PIVOT to get from your current output to your desired output.

However, if there are a fixed number of roles, you could also include that in your joins from the outset:

SELECT wo.Day, wo.Employee, e.name, er1.name, er2.name, etc.
FROM workoutput AS wo
INNER JOIN [employee] AS e
    ON workoutput.[Employee ID] = e.id

LEFT JOIN employeeInfo AS ei1
    ON e.ID = ei1.child
    AND ei1.relation = 1
LEFT JOIN [employee] AS er1
    ON ei1.parent = er1.id

LEFT JOIN employeeInfo AS ei2
    ON e.ID = ei2.child
    AND ei2.relation = 2
LEFT JOIN [employee] AS er2
    ON ei2.parent = er2.id

etc.

---

Not with standard SQL. The usual approach here would be to sort the table and then use it as input into a reporting tool or a custom program.

In SQL Server, it would be possible to write a stored procedure which did this. It depends on how badly you want it.

---

you can group on employee identifier and group_concat the parents.

http://explainextended.com/2010/06/21/group_concat-in-sql-server/

---

if your relation field is constant (i.e. only a set number of relationship types) you can write a CASE statement to transpose it like this...

SELECT workoutput.Day, workoutput.Employee, 
CASE relation WHEN 2 THEN employee.name END AS relation2, CASE relation WHEN 4 THEN employee.name END AS relation4
FROM workoutput 
INNER JOIN employeeInfo ON workoutput.ID = [employeeinfo].son 
INNER JOIN [employee] ON [employeeInfo].parent = [employee].id 
INNER JOIN [employee] AS [employee1] ON workoutput.[Employee ID] = [employee1].id
GROUP BY workoutput.Day, workoutput.Employee, employee.name

However, a rather glaring downside to this is that if you add new relations then you will start to get duplicate rows so this should only be used if you have rigorous version controlling in your SP's or similar

EDIT Apologies, I missed off the GROUP BY clause :)