patternsqlMinor
Merge multiple rows into one row with sql
Viewed 0 times
rowsmergewithintosqlonemultiplerow
Problem
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:
Result:
What I want is result like:
```
Employee Year Period Activity Day1 Day2 Day3 Day4 Day5 Day6 Da
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 0What I want is result like:
```
Employee Year Period Activity Day1 Day2 Day3 Day4 Day5 Day6 Da
Solution
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;Code Snippets
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;Context
StackExchange Database Administrators Q#160395, answer score: 5
Revisions (0)
No revisions yet.