SQL Distinct Statement in MS SQL Server Express 2014

I asked a similar question a few weeks ago but now have a different table layout: If there is one table (from a flat CSV file) that includes fields for Employee ID, 20 Year Sales, 10 Year Sales, 5 Year Sales, and 1 Year Sales, and the Employee ID field is not currently unique, what SELECT statement could be used to return a DISTINCT Employee ID field with each of the Sales figures populated for each Employee ID  if sales are available for that Employee ID during that sales period ?  To further describe the table or the flat CSV file there are about 1500 records that include about 1000 unique Employee ID fields.  Each employee would be listed if that employee was in the Top 200 for sales for a given period, each Employee ID may show up in one or more sales periods with a single record for each sales period, and all employee records for all sales periods have been combined into a single file.

So, for example, Employee ID 121 may be listed three times: in the 20 Year Sales with a 20 Year Sales total amount; in the 5 Year Sales with a 5 Year Sales amount total; and in the 1 Year Sales with a 1 Year Sales Amount total.  Other employees may be listed for one or more of the sales periods, again with each sales period figure having a different record or row.  So for each employee ID the desired query results set would then be unique and show all associated records a single time such as:

Employee ID            20 Yr Sales        10 Yr Sales        5 Yr Sales          1 Yr Sales
121                             $150,000                                       $40,000              $15,000


Instead of showing the following as it appears in the flat CSV file currently:


Employee ID            20 Yr Sales        10 Yr Sales        5 Yr Sales          1 Yr Sales
121                             $150,000                                      
121                                                                                    $40,000            
121                                                                                                                $15,000


Hopefully I detailed that correctly, and TIA for any help on this,
LGroup1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
you need to use a pivot query. Can you post your original query and/or your table structure?
LGroup1Author Commented:
That was basically, the table structure with the Name filed omitted. So the table structure is:


Employee ID,     Employee Name,    20 Year Sales,    10 Year Sales,    5 Year Sales,      1 Year Sales


The data is actually just from a flat CSV file without column headings, although the column headings were added when the data was imported into SQL Server Express.

Thanks,
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You'll just need this:
select "Employee ID", "Employee Name" = max("Employee Name"), "20 Year Sales" = sum("20 Year Sales"), "10 Year Sales" = sum("19 Year Sales"), "5 Year Sales" = sum("5 Year Sales"), "1 Year Sales" = sum("1 Year Sales")
from tbl
group by "Employee ID"

Open in new window

Whether you use min, max, or sum for the sales figures should not make a difference if there is a single row with values for each sale.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LGroup1Author Commented:
The Group By clause worked great, thanks !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.