troubleshooting Question

SQL Concatenate 3 Fields and Always Order Alphabetical Across

Avatar of dpmoney
dpmoneyFlag for United States of America asked on
* T-SQLMicrosoft SQL ServerSQL
14 Comments1 Solution207 ViewsLast Modified:

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
      Else d.SalesRep
      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'

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros