SQL Query Joins

Question

I have a table as follows:

name        week    effort
quentin     1       1
quentin     1       2
quentin     2       1
tracy       1       1       
joe         2       2

There will only be a handful of unique names so it doesn't need to be dynamic

And I would like to query it to return something like

week    QuentinEffortSum    TracyEffortSum  JoeEffortSum
1       3                   1               0
2       1                   0               2

I have tried something along the lines of

SELECT SUM(Effort) AS JoeEffort, Min (Week) AS week FROM [Group$]
WHERE name = "Joe"
GROUP BY week
ORDER By week

which returned:

week   JoeEffort
1      3
2      1

and now I need the other columns and imagine in involves joins but am not sure how to complete the task

Please help

Thanks

what if you have 1000's of names..?do you need a column with effort sum for each name? — Vamsi Prabhala, Dec 23 '15 at 20:51
@lad2025 . . . Amusingly, I notice the headers almost spell "weak effort". — Gordon Linoff, Dec 23 '15 at 20:52
Not an exact duplicate, but the answer here is the same: http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — Tab Alleman, Dec 23 '15 at 20:53
What have you tried? What have you done to try to figure out your problem? — Duston, Dec 23 '15 at 20:54
@vpk Sorry I should've specified that there are only a few unique names, but yes I desire one column per person — Donahue1337, Dec 23 '15 at 20:59

score 1 · Accepted Answer · answered Dec 23 '15 at 21:19

1

I think a PIVOT table would work, like so:

SELECT *
FROM (
    SELECT 
        week,name,effort 
    FROM [Group$]
) as s
PIVOT
(
    SUM(effort)
    FOR [name] IN ('quentin','joe','tracy')
)AS pvt

answered Dec 23 '15 at 21:19

sgtrice1

101
4

SQL Query Joins

1 Answers1