Can a simple query achieve this result? 1

[yehong]

I need to write a simple query that achieves following results.

Data in a table say Table1.

EmpID StartDate CurrentStatus Grade
10001 04/01/2006 A Null
10001 05/01/2006 Null G-A

The result set that I expect should look like following in one row:
EmpID Year CurrentStatus Grade
10001 2006 A G-A

Thanks,

 

[bborissov]

SELECT EmpId, YEAR(StartDate)        AS [Year],
       MAX(ISNULL(CurrentStatus,[])) AS CurrentStatus,
       MAX(ISNULL(Grade,[]))         AS Grade
FROM Table1
GROUP BY EmpId, YEAR(StartDate)

(not tested)

Borislav Borissov

 

[yehong]

Thanks it was really a very simple query. I modifed my query and it worked like a charm.