asked on

SQL Concatenate 3 Fields and Always Order Alphabetical Across

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'

Jim Horn

>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.

Mike Eghtebas

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.

dpmoney

ASKER

@Jim:

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.

dpmoney

ASKER

@Mike:

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).

dpmoney

ASKER

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.
Orders.txt
SalesReps.txt

Mike Eghtebas

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:

ROW_NUMBER() OVER(PARTITION BY PO ORDER BY SalesRep ASC) as RN

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

Scott Pletcher

Useable sample data, that is, CREATE TABLE and INSERT statements, would be vastly more useful. An image of data is worthless for coding :).

lludden

Ok, first some SQL to create the sample data:

CREATE TABLE Orders
    ([OrderID] int, [OrderNum] int, [SalesRepID] int, [SalesRepID2] int, [SalesRepID3] int, [POCreateDate] datetime)
;
    
INSERT INTO Orders
    ([OrderID], [OrderNum], [SalesRepID], [SalesRepID2], [SalesRepID3], [POCreateDate])
VALUES
    (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')
;


CREATE TABLE SalesReps
    ([SalesRepID] int, [SalesRep] varchar(5))
;
    
INSERT INTO SalesReps
    ([SalesRepID], [SalesRep])
VALUES
    (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)
;WITH SR AS (
SELECT o.[OrderID], sr.[SalesRep]
FROM [Orders] [o] 
INNER JOIN [SalesReps] [sr] ON o.[SalesRepID] = sr.[SalesRepID]
UNION
SELECT o.[OrderID], sr.[SalesRep]
FROM [Orders] [o] 
INNER JOIN [SalesReps] [sr] ON o.[SalesRepID2] = sr.[SalesRepID]
UNION
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
FROM [SR]
),  --Next, we do a pivot on this table to convert the rows to columns
OrderedList AS (
SELECT * 
FROM sr1
PIVOT
(
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

dpmoney

ASKER

@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!

ASKER CERTIFIED SOLUTION

dpmoney

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

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

Scott Pletcher

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
CROSS APPLY (
SELECT
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)

dpmoney

ASKER

Scott, I'll test later this week. Thanks.

Scott Pletcher

Interesting. Well, good luck on future qs.

dpmoney

ASKER

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!