Tomb_Raider_Legend Tomb_Raider_Legend - 6 months ago 8
SQL Question

SQL Server - How to combine columns with same

I have somehow joined up some tables and got some columns out so when I run

SELECT SSN, Name, Year2010/Season1, Year2010/Season2, Year2010/Season3, Year2010/Season4 FROM (SELECT ...... ) AS WORKERS
I get a table showen as below:

SSN Name Year2010/Season1 Year2010/Season2 Year2010/Season3 Year2010/Season4
112 John 5.4
234 Tom 4.2
543 Ben 9.3
234 Tom 3.2


The table is showing the workers and the hours they work at each year and period. However I am trying to combine those duplicate values (same person) and move all the hours into same row depending on the year and season. Something like this (where Tom is moved):

SSN Name Year2010/Season1 Year2010/Season2 Year2010/Season3 Year2010/Season4
112 John 5.4
234 Tom 4.2 3.2
543 Ben 9.3


I have been trying for hours but don't really find a good way to combine these depending on the select statement I am doing to get this table. Any suggestion?

Answer

Seems like you need to use SUM:

SELECT  SSN,
        Name, 
        SUM([Year2010/Season1]) [Year2010/Season1],
        SUM([Year2010/Season2]) [Year2010/Season2],
        SUM([Year2010/Season3]) [Year2010/Season3],
        SUM([Year2010/Season4]) [Year2010/Season4]
FROM dbo.YourTable
GROUP BY SSN,
         Name;