CODEWITHSUNDEEP
databasedb2pivot
cyt
cyt

Reputation: 68

How to make a pivot table in DB2?

I have a table be like:

| Date      | Week | Name   | No | Count |
|-----------|------|--------|----|-------|
| 2019/4/1  |  14  | John   | 1  | 1     |
| 2019/4/1  |  14  | Mary   | 2  | 1     |
| 2019/4/9  |  15  | Kevin  | 3  | 2     |
| 2019/4/9  |  15  | John   | 4  | 1     |
| 2019/4/9  |  15  | Jessie | 5  | 1     |
| 2019/4/18 |  16  | Kevin  | 6  | 1     |
| 2019/4/18 |  16  | John   | 7  | 1     |
| 2019/4/18 |  16  | Jessie | 8  | 2     |
| 2019/4/18 |  16  | Mary   | 9  | 3     |
| 2019/4/18 |  16  | Mary   | 10 | 1     |
| 2019/4/18 |  16  | Jessie | 11 | 1     |
| 2019/4/24 |  17  | Mary   | 12 | 1     |
| 2019/4/24 |  17  | Jessie | 13 | 1     |

What I want to do is to calculate people's total count per Week. And sort by their total count.

I know GROUP BY can make this happen, I've tried, but just can't figure it out.

This is what I expect:

| Name   | 14 | 15 | 16 | 17 | Total |
|--------|----|----|----|----|-------|
| Mary   | 1  | 0  | 4  | 1  | 6     |
| Jessie | 0  | 1  | 3  | 1  | 5     |
| John   | 1  | 1  | 1  | 0  | 3     |
| Kevin  | 0  | 2  | 1  | 0  | 3     |
| Total  | 2  | 4  | 9  | 2  | 17    |

How can I do?

Upvotes: 0

Views: 180

Answers (1)

Michael S.
Michael S.

Reputation: 146

Select [Name] 
 ,sum(case when [Week] = 14 then [Count] else 0 end) as Week14
 ,sum(case when [Week] = 15 then [Count] else 0 end) as Week15
 ,sum(case when [Week] = 16 then [Count] else 0 end) as Week16
 ,sum(case when [Week] = 17 then [Count] else 0 end) as Week17
 ,sum([Count]) as Total
from [table]
group by [Name]
order by Total

I'm not sure which version of DB2 you're using (LUW/zOS/i) so this is a general answer. The week number can be made to be more flexible but a certain amount of hard coding will need to be done for the number of weeks.

Upvotes: 2

Related Questions