In a SQL Server query, I'm trying to figure out how to combine two rows of data into one row for specific records.

The following is an example of table data. Below it is how I would like the data to be displayed. I want to display all available columns for each employee but on 1 row. I tried group by but that did not work as I want all the columns displayed.

I'd like to display only one row for certain employees who have two rows. I can use EMP ID because it is associated with a specific employee. Any suggestions for the best way to accomplish this in SQL Server?

CodePudding user response：

The question lacks some information, but overall it sounds like this is what you need:

SELECT t.dept_name, t.team_name, t.employee_name, t.emp_id,
       max(t.count1) as count1, max(t.count2) as count2, max(t.count3) as count3,
       max(t.start_dt) as start_dt, max(t.end_dt) as end_dt, 
       max(t.load_date) as load_date
FROM YourTable t
GROUP BY t.dept_name, t.team_name, t.employee_name, t.emp_id

I assumed t.dept_name, t.team_name, t.employee_name, t.emp_id is the way you want to group them, and that only one of the two records will hold a value for the counts (if not you need to decide which one you want). Also the dates weren't clear, because for dept a you chose the first one and for the rest you chose the second one, so I used max.