How to get Master and Slave Table data in one row using SQL Server ?

Question

I have main table called 'Employee' and another slave table called 'EmployeeTypes' that has a FK from 'Employee'.

Each row in 'Employee' can have zero or many rows in 'EmployeeTypes' and I want to make an SQL Query that returns data of all Employees and each employee row should contain its related data in 'EmployeeTypes' (for example column called 'TypeID') as a comma separated list, like this:

Meco Beco ---    45   ---- 1,2,3

Answer 1

You can do this by combining a UDF that uses the Coalese method of generating a CSV list, with your standard sql statement.

The udf will look something like:

create function ConcatEmployeeTypes(@employeeid int)
returns varchar(max)
as
begin

declare @result varchar(max)

select @result = coalesce(@result + ', ', '') + employeeType
from employeeTypes
where employeeId = @employeeid

return @result

end

You then can simply call the udf like so:

select employeename, dbo.ConcatEmployeeTypes(employeeid)
from Employees

Answer 2

You will probably need to create a temporary table to 'flatten' the master slave relationship and then dynamically create a query based on the fields in this temp table.

See this earlier question and answers for more details.

Answer 3

    SELECT DISTINCT Name, e2.EmployeeID,ISNULL((SELECT STUFF((SELECT ',' + CAST(EmployeeType AS VARCHAR(20)) 
                    FROM Employee e
                    JOIN EmployeeType et ON (e.EmployeeID = et.EmployeeID)
                    WHERE e.EmployeeID = e2.EmployeeID
                    ORDER BY et.EmployeeType
                    FOR XML PATH('')),1,1,'')),'n/a') [EmployeeTypes]
    FROM Employee e2
    LEFT JOIN EmployeeType et2 ON (e2.EmployeeID = et2.EmployeeID)