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

You need to use an OUTER JOIN; you also need to use something equivalent to the GROUP_CONCAT function found in MySQL and other DBMS. — Jonathan Leffler, Apr 19 '10 at 06:21

Scot Hauder · Answer 1 · 2010-04-19T11:45:14.220

    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)

David Hall · Accepted Answer · 2010-04-19T06:46:26.437

0

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

edited Apr 19 '10 at 06:46

answered Apr 19 '10 at 06:35

David Hall

32,624
10
90
127

Thanks for this solution , but is there any other solution that i can do in one query without using function ? – Amr Badawy Apr 19 '10 at 07:02
@Space Cracker - no, I don't believe that there is such a solution. As far as I know, your only options are a temp table, or a udf. I may be worth looking at the pivot command... I don't think it does waht you want but I've never used it in anger so it might have something for you. – David Hall Apr 19 '10 at 07:17
@David, I already found what i search for in the following http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 but i don't know which is the best in performance , your solution or the one in the above URL ? – Amr Badawy Apr 19 '10 at 07:39
@Space Cracker group_concat is not a sql server function. As far as I know, the only way to simulate it is to use something like my solution. – David Hall Apr 19 '10 at 07:47
@Space Cracker - sorry, didn't read that link properly the first time. I'm not sure which answer would give the best performance. The best way to be sure is to test the performance yourself. Just be careful to test representative data sizes, that can affect things. – David Hall Apr 19 '10 at 07:49

score 0 · Answer 3 · edited May 23 '17 at 10:33

0

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.

edited May 23 '17 at 10:33

Community

1
1

answered Apr 19 '10 at 06:36

Ash

60,973
31
151
169

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

3 Answers3