Sql-server – Merge multiple rows into one row with sql

aggregatesql server

In a source table each row represent hours for one day for a spesific Activity. How can I merge rows of days into rows of periods distinct on Employee, Year, Period and Activity with sql?

Select from source table:

SELECT [Employee]
      ,[Year]
      ,[Period]
      ,[Activity]
      ,[Day1]
      ,[Day2]
      ,[Day3]
      ,[Day4]
      ,[Day5]
      ,[Day6]
      ,[Day7]
FROM [HoursAccounting]
where Employee = '1234'

Result:

Employee Year        Period      Activity   Day1    Day2    Day3    Day4    Day5    Day6    Day7
-------- ----------- ----------- ---------- ------- ------- ------- ------- ------- ------- -------
1234     2016        34          102002     7,5     0       0       0       0       0       0
1234     2016        34          102002     0       7,6     0       0       0       0       0
1234     2016        36          102002     0       7,5     0       0       0       0       0
1234     2016        36          102002     7,5     0       0       0       0       0       0
1234     2016        37          102002     0       4,5     0       0       0       0       0
1234     2016        37          104001     7,5     0       0       0       0       0       0
1234     2016        37          104001     0       0       0       4       0       0       0
1234     2016        37          104002     0       0       7,5     0       0       0       0
1234     2016        39          102002     0       0       7,5     0       0       0       0
1234     2016        39          102002     0       7,5     0       0       0       0       0
1234     2016        39          102002     0       0       0       7,5     0       0       0
1234     2016        39          102002     0       0       0       0       7,5     0       0
1234     2016        39          102002     7,5     0       0       0       0       0       0

What I want is result like:

Employee Year        Period      Activity   Day1    Day2    Day3    Day4    Day5    Day6    Day7
-------- ----------- ----------- ---------- ------- ------- ------- ------- ------- ------- -------
1234     2016        34          102002     7,5     7,6     0       0       0       0       0
1234     2016        36          102002     7,5     7,5     0       0       0       0       0
1234     2016        37          102002     0       4,5     0       0       0       0       0
1234     2016        37          104001     7,5     0       0       4       0       0       0
1234     2016        37          104002     0       0       7,5     0       0       0       0
1234     2016        39          102002     7,5     7,5     7,5     7,5     7,5     0       0

Can it be done in one select?

Best Answer

SELECT [Employee]
      ,[Year]
      ,[Period]
      ,[Activity]
      ,Sum([Day1]) as Day1
      ,Sum([Day2]) as Day2
      ,Sum([Day3]) as Day3
      ,Sum([Day4]) as Day4
      ,Sum([Day5]) as Day5
      ,Sum([Day6]) as Day6
      ,Sum([Day7]) as Day7
FROM [HoursAccounting]
where Employee = '1234'
group by Employee, [Year], Period, Activity;
Related Question