3

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?

  • 1
    This is called a pivot, McNets answer is a good example of a classic pivot in sql. – SqlZim Jan 9 '17 at 13:59
5
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.