We help IT Professionals succeed at work.
Get Started

SQL Concatenate 3 Fields and Always Order Alphabetical Across

dpmoney
dpmoney asked
on
206 Views
Last Modified: 2017-03-29
Hello,

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

-OR-

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?

SELECT CASE
      
      --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'
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 14 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE