SQL: How to select field values and return them as column names in result?

1

Sorry if my question’s title is confusing. It’ll be easier to look at my code/example below.

I am trying to sum each activity stored in my DB by each of their elapsed time (field value) and then group them by week. I feel like I’m close. Currently, this is my query:

SELECT week, activityType,
SUM (elapsedTime) AS activityTotalTime
FROM activity
WHERE year=2017
AND activityType IN ('rockclimbing', 'hike', 'run')
GROUP BY week, activityType;

This produces a result like:

week activityType activityTotalTime
1 run 2.22
2 hike 4.09
2 run 0.73
3 hike 1.2
3 rockclimbing 1.23
3 run 1.37

But what I would really like is:

week run hike rockclimbing
1 2.22 0 0
2 0.72 4.09 0
3 1.37 4.3 0.67

How can I achieve this with SQL? Is this a case for a pivot? How to do that?

2

You are correct. It looks like you do need to use PIVOT. See the example from Oracle’s blog post below for explanation and example:

https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

3

Thanks for your link. I’m still a little stuck on how to use a pivot here, even after reading. I ended up doing a bunch of CASE statements:

SELECT week
, SUM (CASE when activityType = 'rockclimbing' then elapsedTime else 0 END) AS rockclimbing
, SUM (CASE when activityType = 'hike' then elapsedTime else 0 END) AS hike
, SUM (CASE when activityType = 'run' then elapsedTime else 0 END) AS run
FROM activity
WHERE year=2017
AND activityType IN ('rockclimbing', 'hike', 'run')
GROUP BY week
ORDER BY week

Not exactly graceful, I guess - it got the job done however!