SQL Server combine 2 rows into 1-CodePudding

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.

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：

In SQL Server, you can use the GROUP BY clause and an aggregate function to combine multiple rows of data into one for specific records. The following query, for example, will group the rows by EMP ID and return the sum and count of the specified column for each group:

SELECT EMP_ID, SUM(column_name) AS column_name, COUNT(column_name) AS column_name_count
FROM your_table
GROUP BY EMP_ID;

The data will be organized by employee ID, and a summary of the column specified for each group of records with the same employee ID will be provided.

CodePudding user response：

If you simply wish to display the employee IDs without regard to any of the other variables in the table, then this can be accomplished using the DISTINCT function:

select distinct emp_id from table;

This will return employee IDs without any duplicate values being returned.

If you are looking to aggregate data (which I believe is your intention), then it is a case of using an aggregate function such as GROUP BY. e.g. given emp_id and a column x, one example of a query could be as follows:

select emp_id, sum(x) from table group by emp_id order by emp_id;