SQL Server - How to combine columns with same

Question

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?

Gordon Linoff · Accepted Answer · 2016-06-09 18:09:26Z

2

You want aggregation. You might be able to do this more simply by adjusting your query, but you can also do:

with q as (<your query here>)
select ssn, name,
       max([Year2010/Season1]) as  [Year2010/Season1],
       max([Year2010/Season2]) as  [Year2010/Season2],
       max([Year2010/Season3]) as  [Year2010/Season3],
       max([Year2010/Season4]) as  [Year2010/Season4]
from q
group by ssn, name;

answered Jun 9, 2016 at 18:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dfundako Over a year ago

What happens if there are multiple rows for an employee with values during the same time period?

Lamak · Accepted Answer · 2016-06-09 18:08:42Z

1

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;

answered Jun 9, 2016 at 18:08

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

2 Comments

Tomb_Raider_Legend Over a year ago

Thanks for your respond. I forgot to mention the values of hours are set as VARCHAR. However I used this method and changed the SUM to MAX and it seems to work.

Tomb_Raider_Legend Over a year ago

Is there any way to order the values by SSN?

Collectives™ on Stack Overflow

SQL Server - How to combine columns with same

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related