LGroup1
asked on
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,
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,
YZlat
you need to use a pivot query. Can you post your original query and/or your table structure?
ASKER
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,
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The Group By clause worked great, thanks !