CODEWITHSUNDEEP
sqlsql-server
doctordoctor66
doctordoctor66

Reputation: 3

SQL merge and sum multiple rows into one

Essentially I have a SQL table that looks like this;

Name   Week 1  Week 2  Week 3  Week 4  Week 5
James  NULL    5       NULL    NULL    NULL
James  10      NULL    NULL    NULL    NULL
James  NULL    NULL    NULL    5       NULL
James  NULL    NULL    NULL    20      NULL
Tom    NULL    NULL    5       NULL    NULL
Tom    NULL    10      NULL    NULL    NULL
Tom    25      NULL    NULL    NULL    NULL
Tom    NULL    NULL    NULL    5       NULL
Tom    NULL    NULL    NULL    5       NULL

And I would want to combine together the rows (while also summing the values) to get something more like this;

Name   Week 1  Week 2  Week 3  Week 4  Week 5
James  10      5       20      25      NULL
Tom    25      10      5       10      NULL

But I can't think of a good way of doing so. My data has quite the number of columns and rows so I'm looking for something which ideally doesn't require listing out all the individual column or row names.

Thanks

Upvotes: 0

Views: 4609

Answers (1)

Scott Dobbins
Scott Dobbins

Reputation: 294

Just Sum the Rows and Group by name

 select 
     Name
   , Week1 = sum(Week 1)
   , Week2 = sum(Week 2)
   , Week3 = sum(Week 3) 
   , Week4 = sum(Week 4) 
   , Week5 = sum(Week 5) 
   from Table 
   group by Name

Upvotes: 3

Related Questions