Link to home
Avatar of dpmoney
dpmoneyFlag for United States of America

asked on

SQL Concatenate 3 Fields and Always Order Alphabetical Across


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'
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>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.
Why's that?  The purpose of a database is to store data, and not in any sort order such as A-Z, as that's the role of queries and reports.

>We need results to always show concatenated in alphabetical order:    Mary, Nick Roger
Do you mean comma-separated and in a single column?  If yes, give this article a look and confirm this is what you're asking for.
re> Let's say we have 5 Sales Reps:  Nick, John, Jane, Mary, Roger

3 salesperson per PO? What happens with the other two. Are they ignored?

Post some data. after we make a table from your data, then we could test the code prior to posting it.
Avatar of dpmoney



Thanks for your feedback.  

Regarding your second point, yes, I'm looking to show the output as a single concatenated field separated by commas (which I can do), but the issue is I need the resulting 3 names in the concatenated, single output field to always be in alphabetical order (e.g. Mary, Nick, Roger).

I looked at your published article, but it doesn't seem to be an exact match with what I'm looking to accomplish.  The cartoon characters do not seem to be concatenated in alphabetical order, nor does your example include a case statement.
Avatar of dpmoney



This is based on an example excerpt of our system with fictitious data.  
In my example, there are 5 Sales Reps in total that a data entry person can select from a drop down list on a form.
I arbitrarily picked 3 of them that would be associated with a single order for illustration purposes (they work in teams and get joint credit).
Avatar of dpmoney


Here is an example excerpt of the Orders and Sales Reps table with applicable columns

I have only included rows that have all 3 sales rep slots filled in since that is the hurdle I'm trying to clear.  Thank you.
At this point, it seems this to be a very easy thing to do. Below, I will show what I have in mind and based on that, I have a few additional question you could answer:


1. Where is PO column in the sample data you have provided if there will be 3 salesmen per PO needs to be handled. May be all of the sample data is for one PO?
2. RN will number the salesmen but in WHERE clause we can limit it to RN=3
Useable sample data, that is, CREATE TABLE and INSERT statements, would be vastly more useful.  An image of data is worthless for coding :).
Ok, first some SQL to create the sample data:

    ([OrderID] int, [OrderNum] int, [SalesRepID] int, [SalesRepID2] int, [SalesRepID3] int, [POCreateDate] datetime)
    ([OrderID], [OrderNum], [SalesRepID], [SalesRepID2], [SalesRepID3], [POCreateDate])
    (1, 1001, 2, 1, 3, '2017-01-03 00:00:00'),
    (2, 1002, 4, 2, 1, '2017-02-17 00:00:00'),
    (3, 1003, 1, 4, 5, '2017-02-23 00:00:00'),
    (4, 1004, 5, 1, 4, '2017-03-01 00:00:00'),
    (5, 1005, 1, 3, 2, '2017-03-03 00:00:00'),
	(6,1006, 1,6,NULL,'2017-02-11 00:00:00')

    ([SalesRepID] int, [SalesRep] varchar(5))
    ([SalesRepID], [SalesRep])
    (1, 'Nick'),
    (2, 'John'),
    (3, 'Jane'),
    (4, 'Mary'),
    (5, 'Roger'),
	(6, 'Buddy')

Open in new window

--First we break the Order table back into normalized data (SR)
SELECT o.[OrderID], sr.[SalesRep]
FROM [Orders] [o] 
INNER JOIN [SalesReps] [sr] ON o.[SalesRepID] = sr.[SalesRepID]
SELECT o.[OrderID], sr.[SalesRep]
FROM [Orders] [o] 
INNER JOIN [SalesReps] [sr] ON o.[SalesRepID2] = sr.[SalesRepID]
SELECT o.[OrderID], sr.[SalesRep]
FROM [Orders] [o] 
INNER JOIN [SalesReps] [sr] ON o.[SalesRepID3] = sr.[SalesRepID]
),  --Now we build build a query that lists all of the reps in alpha order by name
SR1 AS (
SELECT sr.[OrderID], sr.[SalesRep], ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY [SalesRep] ASC) AS AlphaOrder
),  --Next, we do a pivot on this table to convert the rows to columns
OrderedList AS (
FROM sr1
MAX([SalesRep]) FOR  AlphaOrder IN ([1],[2],[3])
) AS P
) -- Finally we concatenate the three columns we have created (which will be in order) back to a single column and rejoin to the original table 
SELECT o.OrderID, [1] + ',' + ISNULL([2],'') + ',' + ISNULL([3],'') AS SalesReps, o.[OrderNum], o.[POCreateDate]
FROM [OrderedList]
INNER JOIN [Orders] [o] ON [OrderedList].[OrderID] = o.[OrderID]

Open in new window

Result is
OrderID	SalesReps	OrderNum	POCreateDate
1	Jane,John,Nick	1001	2017-01-03 00:00:00.000
2	John,Mary,Nick	1002	2017-02-17 00:00:00.000
3	Mary,Nick,Roger	1003	2017-02-23 00:00:00.000
4	Mary,Nick,Roger	1004	2017-03-01 00:00:00.000
5	Jane,John,Nick	1005	2017-03-03 00:00:00.000
6	Buddy,Nick,	1006	2017-02-11 00:00:00.000

Open in new window

Avatar of dpmoney


@Mike.....Each line in my example dataset represents a unique PO (purchase order).  I'm still trying to review your response to see if it fits.

@Scott....the data I provided as a sample is not an  image, but rather, selectable tab delimited text that could quickly be added to create and insert example (as was done by lludden).

@lludden....this is a very comprehensive response (thank you) and I can see where you are going with it, but I'm back at the original problem....needing to nest a With statement within a CASE Statement.  The syntax rules don't seem to like it and I need to plug the code into section noted in my original example above.  

At the end of the day, I just need to take 3 known, non-null field values from each record in a SQL table, then concatenate them as one output field (in alphabetical order across) separated by commas.  @lluden has it closest, but the tricky part is I need that code to work in the correct part of a CASE statement.  Please see my example query above.

Thanks to everyone for input so far!
Avatar of dpmoney
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
The code below could easily be extended to 5, or more, SalesReps:

SELECT O.OrderID, O.OrderNum, /*...*/
    ISNULL(CA1.SalesRep1, '') + ISNULL(', ' + CA1.SalesRep2, '') + ISNULL(', ' + CA1.SalesRep3, '') AS SalesReps
FROM #Order O
        MAX(CASE WHEN row_num = 1 THEN SalesRep END) AS SalesRep1,
        MAX(CASE WHEN row_num = 2 THEN SalesRep END) AS SalesRep2,
        MAX(CASE WHEN row_num = 3 THEN SalesRep END) AS SalesRep3
    FROM (
        SELECT SR.SalesRep, ROW_NUMBER() OVER(ORDER BY SR.SalesRep) AS row_num
        FROM (
            VALUES(O.[SalesRepID]), (O.[SalesRepID2]), (O.[SalesRepID3])
        ) AS SalesRepIDs
        INNER JOIN #SalesReps SR ON SR.SalesRepID = SalesRepIDs.SalesRepID
    ) AS Derived
) AS CA1(SalesRep1,SalesRep2,SalesRep3)
Avatar of dpmoney


Scott, I'll test later this week.  Thanks.
Interesting.  Well, good luck on future qs.
Avatar of dpmoney


The solution I came up with was long-winded, but it was easiest to merge into the case statement shown in my original question.  Thanks to everyone for their input!