I have an Orders Table that allows up to 3 Sales Reps to be associated with a PO.
It will be difficult to force the Sales Reps to be entered alphabetically at time of data entry so we need to do it in SQL.
For example, Let's say we have 5 Sales Reps: Nick, John, Jane, Mary, Roger
Regardless of whether someone enters the names on the PO (using drop down list) as:
SalesRep1 = Nick
SalesRep2 = Mary
SalesRep3 = Roger
SalesRep1 = Roger
SalesRep2 = Mary
SalesRep3 = Nick
We need results to always show concatenated in alphabetical order: Mary, Nick Roger
We have an example SQL statement below that handles conditions for when there is only 1 or 2 sales reps on file, but for 3, it gets exponentially more complex in terms of possibilities. I've tired some (with CTE unpivot) code, but it doesn't seem to work in the Case statement. Lots of syntax errors. Any thoughts?
--Only 1 Sales Rep on file
When O.SalesRepID3 is Null and O.SalesRepID2 is null Then D.SalesRep
--Only 2 Sales Reps on file
When O.SalesRepID3 is null and O.SalesRepID2 is not null and O.SalesRepID2 < O.SalesRepID Then D2.SalesRep + ', ' + D.SalesRep
When O.SalesRepID3 is null and O.SalesRepID2 is not null and O.SalesRepID2 > O.SalesRepID Then D.SalesRep + ', ' + D2.SalesRep
--All 3 Sales Reps on file
When O.SalesRepID3 is not null and O.SalesRepID2 is not null then
******** Need the code to concatenate all 3 sales rep fields and alphabetize them in this part of case statement *******
-- Catch All Condition
End as [SalesRep]
from Orders O
LEFT JOIN SalesReps D ON D.SalesRepID = O.SalesRepID
LEFT JOIN SalesReps D2 ON D2.SalesRepID = O.SalesRepID2
LEFT JOIN SalesReps D3 ON D3.SalesRepID = O.SalesRepID3
Where O.PODate >= '1/1/17'