Help with Distinct group or concat on multiple almost duplicate record

[jpack23]

Hi,

Here's my problem....I have an "employee" table and an "activity" table and an "ActivityEmployeeDetail" table.

one activity can have multiple employees and the details of that activity are stored in the "ActivityEmployeeDetail" one record for each employee

Activity table

ActivityID Date
1 1/1/2010
2 2/2/2010
3 3/3/2010

Employee Table

EmployeeID EmpLastName
45 smith
88 green
99 Johnes
33 lopex
22 Person


ActivityEmployeeDetail table

AcitityDetailID ActivityID EmployeeID detail
1 1 45 blah blah
2 1 88 stuff
3 2 99 blah blah
4 3 33 blah blah
5 3 22 blah blah

I have this big query that returns the following results

ActivityID EmployeeID EmpLastName Date
1 45 Smith 1/1/2010
1 88 Greem 1/1/2010
2 99 Johnes 2/2/2010
3 33 Lopex 3/3/2010
3 22 Person 3/3/2010

I would like the query to return a result that show only the 1st record found of each activiy.

ActivityId EmployeeID EmpLastName date
1 45 smith 1/1/2010
2 99 johnes 2/2/2010
3 33 Lopex 3/3/2010

in this example even though ActivityIds 1 and 3 have 2 records each the query would only return the 1st


or can I concat the employeeID and EmpLastName fields to return records like this

ActivityID EmployeeID EmpLastName Date
1 45,88 smith,green 1/1/2010
2 99 johnes 2/2/2010
3 33,22 Lopex,Person 3/3/2010


my original query looks like this

Select a.activityID,ae.employeeID,e.LastName,a.date
from Activity a
inner join activityEmployee ae on a.activityid = ae.activityid
inner join employee e on ae.employeeID = e.employeeID
order by a.date

thanks for your help

joe

 

[markros]

SQL Server 2005 and up:

Select a.activityID,stuff((select ',' + cast(ae.employeeID as varchar(10)) 
from activityEmployee ae 
where a.activityid = ae.activityid 
for XML PATH('')),1,1,'') as EmployeeIDs, 
stuff((select ', ' + e.LastName 
from activityEmployee ae 
inner join employee e on ae.employeeID = e.employeeID 
where  a.activityid = ae.activityid 
for XML PATH('')),1,2,'') as EmployeeNames,
a.date
from Activity a
order by a.date

From the top of my head - not tested.


PluralSight Learning Library

 

[jpack23]

thanks markros, solved my problem

 

[Rhys666]

To only get the first record you can also use a CTE and RANK(). I'm not sure which is quicker or more efficient without checking against actual tables and data and this is not tested either unfortunately...

WITH ActivityEmployee_CTE(ActivityID, EmployeeID, EmpLastName, Date, Ranking)
AS
(
SELECT 
    a.activityID
    ,ae.employeeID
    ,e.LastName
    ,a.date
    ,RANK() OVER
    (
        PARTITION BY
            a.activityID
        ORDER BY
            ae.employeeID ASC
    )
FROM
    Activity a
JOIN 
    activityEmployee ae on a.activityid = ae.activityid
JOIN
   employee e on ae.employeeID = e.employeeID
order by a.date
)
SELECT
    ActivityID
    ,EmployeeID
    ,EmpLastName
    ,Date
FROM
    ActivityEmployee_CTE
WHERE
    Ranking = 1

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett