Merge multiple rows into one row with sql

Question

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?

• SqlZim: This is called a pivot, McNets answer is a good example of a classic pivot in sql.

Answer 1

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;